Ejemplo 1: excel vba buscar obtener la última fila de la columna
'VBA to get the last row in column A that has a value.
'Change `A` to the column you care about:
With Sheet1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'A shorter way:
LastRow = Sheet1.[A1048576].End(xlUp).Row
'And to get the row of the last cell with a value
'just above the FIRST blank cell VERTICALLY:
LastRow = Sheet1.[A1].End(xlDown).Row
'----------------------------------------------------------
'The most reliable way to get the last row with data
'for the entire sheet is to use the following function:
LastRow = LastUsedRow(Sheet1.[A1])
Function LastUsedRow(r As Range)
On Error Resume Next
r.Parent.AutoFilterMode = False
With r.Cells.Find("*", r, xlFormulas, , , xlPrevious)
LastUsedRow = 1
LastUsedRow = .Row
End With
End Function
Ejemplo 2: excel vba buscar la última columna
'VBA to get the last column in row 10 that has a value.
'Change `10` to the row you care about:
With Sheet1
LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column
End With
'A shorter way:
LastCol = Sheet1.[XFD10].End(xlToLeft).Column
'And to get the column of the last cell with a value
'just to the left of the FIRST blank cell HORIZONTALLY:
LastCol = Sheet1.[A10].End(xlToRight).Column
'----------------------------------------------------------
'The most reliable way to get the last column with data
'for the entire sheet is to use the following function:
LastCol = LastUsedRow(Sheet1.[A1])
Function LastUsedCol(r As Range)
On Error Resume Next
r.Parent.AutoFilterMode = False
With r.Cells.Find("*", r, xlFormulas, , , xlPrevious)
LastUsedCol = 1
LastUsedCol = .Column
End With
End Function
¡Haz clic para puntuar esta entrada!
(Votos: 1 Promedio: 5)