excel-vba
Methoden voor het vinden van de laatst gebruikte rij of kolom in een werkblad
Zoeken…
Opmerkingen
U kunt hier een goede uitleg vinden waarom andere methoden worden ontmoedigd / onjuist zijn: http://stackoverflow.com/a/11169920/4628637
Zoek de laatste niet-lege cel in een kolom
In dit voorbeeld zullen we kijken naar een methode voor het retourneren van de laatste niet-lege rij in een kolom voor een gegevensset.
Deze methode werkt ongeacht de lege gebieden binnen de gegevensset.
Voorzichtigheid is echter geboden als het gaat om samengevoegde cellen , omdat de End
methode wordt "gestopt" tegen een samengevoegd gebied, waarbij de eerste cel van het samengevoegde gebied wordt geretourneerd.
Bovendien worden niet-lege cellen in verborgen rijen niet in aanmerking genomen.
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
Om de hierboven aangegeven beperkingen aan te pakken, de regel:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
kan worden vervangen door:
voor de laatst gebruikte rij van
"Sheet1"
:
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count
.voor de laatste niet-lege cel van kolom
"A"
in"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
Zoek laatste rij met behulp van benoemd bereik
Als u een benoemd bereik in uw blad hebt en u wilt dynamisch de laatste rij van dat dynamische benoemde bereik krijgen. Omvat ook gevallen waarin het Benoemde bereik niet vanaf de eerste rij begint.
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
Bijwerken:
Een mogelijke maas in de wet werd opgemerkt door @Jeeped voor een genoemd bereik met niet-aaneengesloten rijen omdat dit een onverwacht resultaat oplevert. Om dat probleem aan te pakken, wordt de code herzien zoals hieronder.
Aannames: targes sheet = form
, genaamd range = 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
Krijg de rij van de laatste cel in een bereik
'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
Zoek de laatste niet-lege kolom in het werkblad
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
U kunt kiezen uit 2 opties, afhankelijk van of u wilt weten of er geen gegevens in het werkblad staan:
- NEE: gebruik LastCol_1: u kunt het rechtstreeks gebruiken binnen
wS.Cells(...,LastCol_1(wS))
- JA: gebruik LastCol_0: U moet testen of het resultaat dat u van de functie krijgt 0 is of niet voordat u het gebruikt
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
De eigenschappen van het Err-object worden automatisch gereset naar nul bij het verlaten van de functie.
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
Laatste cel in Range.CurrentRegion
Range.CurrentRegion
is een rechthoekig Range.CurrentRegion
omringd door lege cellen. Lege cellen met formules zoals =""
of '
worden niet als leeg beschouwd (zelfs niet door de ISBLANK
Excel-functie).
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)
Zoek de laatste niet-lege rij in werkblad
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
U kunt kiezen uit 2 opties, afhankelijk van of u wilt weten of er geen gegevens in het werkblad staan:
- NEE: gebruik LastRow_1: u kunt het rechtstreeks gebruiken binnen
wS.Cells(LastRow_1(wS),...)
- JA: gebruik LastRow_0: U moet testen of het resultaat dat u van de functie krijgt 0 is of niet voordat u het gebruikt
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
Zoek de laatste niet-lege cel op een rij
In dit voorbeeld zullen we kijken naar een methode voor het retourneren van de laatste niet-lege kolom in een rij.
Deze methode werkt ongeacht de lege gebieden binnen de gegevensset.
Voorzichtigheid is echter geboden als het gaat om samengevoegde cellen , omdat de End
methode wordt "gestopt" tegen een samengevoegd gebied, waarbij de eerste cel van het samengevoegde gebied wordt geretourneerd.
Bovendien worden niet-lege cellen in verborgen kolommen niet in aanmerking genomen.
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
Zoek de laatste niet-lege cel in werkblad - Prestaties (array)
- De eerste functie, met behulp van een array, is veel sneller
- Als dit wordt aangeroepen zonder de optionele parameter, wordt standaard
.ThisWorkbook.ActiveSheet
- Als het bereik leeg is, wordt standaard
Cell( 1, 1 )
geretourneerd in plaats vanNothing
Snelheid:
GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds
. Gemeten met 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