excel-vba
Métodos para encontrar la última fila o columna utilizada en una hoja de trabajo
Buscar..
Observaciones
Puede encontrar una buena explicación sobre por qué otros métodos son desalentados / inexactos aquí: http://stackoverflow.com/a/11169920/4628637
Encuentre la última celda no vacía en una columna
En este ejemplo, veremos un método para devolver la última fila no vacía en una columna para un conjunto de datos.
Este método funcionará independientemente de las regiones vacías dentro del conjunto de datos.
Sin embargo, se debe tener cuidado si las celdas combinadas están involucradas , ya que el método End
se "detendrá" contra una región fusionada, devolviendo la primera celda de la región fusionada.
Además, no se tendrán en cuenta las celdas no vacías en filas ocultas .
Sub FindingLastRow()
Dim wS As Worksheet, LastRow As Long
Set wS = ThisWorkbook.Worksheets("Sheet1")
'Here we look in Column A
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
Debug.Print LastRow
End Sub
Para abordar las limitaciones indicadas anteriormente, la línea:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
puede ser reemplazado por:
Para la última fila usada de
"Sheet1"
:
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count
.para la última celda no vacía de la Columna
"A"
en"Sheet1"
:Dim i As Long For i = LastRow To 1 Step -1 If Not (IsEmpty(Cells(i, 1))) Then Exit For Next i LastRow = i
Encuentra la última fila usando el rango con nombre
En caso de que tenga un rango con nombre en su hoja, y quiera obtener dinámicamente la última fila de ese rango con nombre dinámico. También cubre los casos en los que el rango con nombre no se inicia desde la primera fila.
Sub FindingLastRow()
Dim sht As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Set sht = ThisWorkbook.Worksheets("form")
'Using Named Range "MyNameRange"
FirstRow = sht.Range("MyNameRange").Row
' in case "MyNameRange" doesn't start at Row 1
LastRow = sht.Range("MyNameRange").Rows.count + FirstRow - 1
End Sub
Actualizar:
@Jeeped señaló una posible laguna para un rango con nombre con filas no contiguas, ya que genera un resultado inesperado. Para resolver ese problema, el código se revisa como se muestra a continuación.
Supuestos: hoja de targes = form
, rango denominado = MyNameRange
Sub FindingLastRow()
Dim rw As Range, rwMax As Long
For Each rw In Sheets("form").Range("MyNameRange").Rows
If rw.Row > rwMax Then rwMax = rw.Row
Next
MsgBox "Last row of 'MyNameRange' under Sheets 'form': " & rwMax
End Sub
Obtener la fila de la última celda en un rango
'if only one area (not multiple areas):
With Range("A3:D20")
Debug.Print .Cells(.Cells.CountLarge).Row
Debug.Print .Item(.Cells.CountLarge).Row 'using .item is also possible
End With 'Debug prints: 20
'with multiple areas (also works if only one area):
Dim rngArea As Range, LastRow As Long
With Range("A3:D20, E5:I50, H20:R35")
For Each rngArea In .Areas
If rngArea(rngArea.Cells.CountLarge).Row > LastRow Then
LastRow = rngArea(rngArea.Cells.CountLarge).Row
End If
Next
Debug.Print LastRow 'Debug prints: 50
End With
Encuentre la última columna no vacía en la hoja de trabajo
Private Sub Get_Last_Used_Row_Index()
Dim wS As Worksheet
Set wS = ThisWorkbook.Sheets("Sheet1")
Debug.Print LastCol_1(wS)
Debug.Print LastCol_0(wS)
End Sub
Puede elegir entre 2 opciones, con respecto a si desea saber si no hay datos en la hoja de trabajo:
- NO: Use LastCol_1: puede usarlo directamente dentro de
wS.Cells(...,LastCol_1(wS))
- SÍ: use LastCol_0: debe probar si el resultado que obtiene de la función es 0 o no antes de usarlo
Public Function LastCol_1(wS As Worksheet) As Double
With wS
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastCol_1 = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
LastCol_1 = 1
End If
End With
End Function
Las propiedades del objeto Err se restablecen automáticamente a cero al salir de la función.
Public Function LastCol_0(wS As Worksheet) As Double
On Error Resume Next
LastCol_0 = wS.Cells.Find(What:="*", _
After:=ws.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function
Última celda en Range.CurrentRegion
Range.CurrentRegion
es un área de rango rectangular rodeada por celdas vacías. Las celdas en blanco con fórmulas como =""
o '
no se consideran en blanco (incluso por la función ISBLANK
Excel).
Dim rng As Range, lastCell As Range
Set rng = Range("C3").CurrentRegion ' or Set rng = Sheet1.UsedRange.CurrentRegion
Set lastCell = rng(rng.Rows.Count, rng.Columns.Count)
Encuentre la última fila no vacía en la hoja de trabajo
Private Sub Get_Last_Used_Row_Index()
Dim wS As Worksheet
Set wS = ThisWorkbook.Sheets("Sheet1")
Debug.Print LastRow_1(wS)
Debug.Print LastRow_0(wS)
End Sub
Puede elegir entre 2 opciones, con respecto a si desea saber si no hay datos en la hoja de trabajo:
- NO: Use LastRow_1: puede usarlo directamente dentro de
wS.Cells(LastRow_1(wS),...)
- SÍ: use LastRow_0: debe probar si el resultado que obtiene de la función es 0 o no antes de usarlo
Public Function LastRow_1(wS As Worksheet) As Double
With wS
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastRow_1 = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
LastRow_1 = 1
End If
End With
End Function
Public Function LastRow_0(wS As Worksheet) As Double
On Error Resume Next
LastRow_0 = wS.Cells.Find(What:="*", _
After:=ws.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Function
Encuentra la última celda no vacía en una fila
En este ejemplo, veremos un método para devolver la última columna no vacía en una fila.
Este método funcionará independientemente de las regiones vacías dentro del conjunto de datos.
Sin embargo, se debe tener cuidado si las celdas combinadas están involucradas , ya que el método End
se "detendrá" contra una región fusionada, devolviendo la primera celda de la región fusionada.
Además, no se tendrán en cuenta las celdas no vacías en columnas ocultas .
Sub FindingLastCol()
Dim wS As Worksheet, LastCol As Long
Set wS = ThisWorkbook.Worksheets("Sheet1")
'Here we look in Row 1
LastCol = wS.Cells(1, wS.Columns.Count).End(xlToLeft).Column
Debug.Print LastCol
End Sub
Buscar la última celda no vacía en la hoja de trabajo - Rendimiento (matriz)
- La primera función, usando una matriz, es mucho más rápida
- Si se llama sin el parámetro opcional, se establecerá de forma predeterminada en
.ThisWorkbook.ActiveSheet
- Si el rango está vacío, se devolverá la
Cell( 1, 1 )
como predeterminada, en lugar deNothing
Velocidad:
GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds
.Medido con MicroTimer
Public Function GetLastCell(Optional ByVal ws As Worksheet = Nothing) As Range
Dim uRng As Range, uArr As Variant, r As Long, c As Long
Dim ubR As Long, ubC As Long, lRow As Long
If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
Set uRng = ws.UsedRange
uArr = uRng
If IsEmpty(uArr) Then
Set GetLastCell = ws.Cells(1, 1): Exit Function
End If
If Not IsArray(uArr) Then
Set GetLastCell = ws.Cells(uRng.Row, uRng.Column): Exit Function
End If
ubR = UBound(uArr, 1): ubC = UBound(uArr, 2)
For r = ubR To 1 Step -1 '----------------------------------------------- last row
For c = ubC To 1 Step -1
If Not IsError(uArr(r, c)) Then
If Len(Trim$(uArr(r, c))) > 0 Then
lRow = r: Exit For
End If
End If
Next
If lRow > 0 Then Exit For
Next
If lRow = 0 Then lRow = ubR
For c = ubC To 1 Step -1 '----------------------------------------------- last col
For r = lRow To 1 Step -1
If Not IsError(uArr(r, c)) Then
If Len(Trim$(uArr(r, c))) > 0 Then
Set GetLastCell = ws.Cells(lRow + uRng.Row - 1, c + uRng.Column - 1)
Exit Function
End If
End If
Next
Next
End Function
'Returns last cell (max row & max col) using Find
Public Function GetMaxCell2(Optional ByRef rng As Range = Nothing) As Range 'Using Find
Const NONEMPTY As String = "*"
Dim lRow As Range, lCol As Range
If rng Is Nothing Then Set rng = Application.ThisWorkbook.ActiveSheet.UsedRange
If WorksheetFunction.CountA(rng) = 0 Then
Set GetMaxCell2 = rng.Parent.Cells(1, 1)
Else
With rng
Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)
If Not lRow Is Nothing Then
Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns)
Set GetMaxCell2 = .Parent.Cells(lRow.Row, lCol.Column)
End If
End With
End If
End Function
.
Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
Dim cyTicks1 As Currency
Static cyFrequency As Currency
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency 'Get frequency
getTickCount cyTicks1 'Get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function