excel-vba
Methoden zum Suchen der zuletzt verwendeten Zeile oder Spalte in einem Arbeitsblatt
Suche…
Bemerkungen
Eine gute Erklärung, warum andere Methoden nicht empfohlen werden, ist hier zu finden: http://stackoverflow.com/a/11169920/4628637
Suchen Sie die letzte nicht leere Zelle in einer Spalte
In diesem Beispiel betrachten wir eine Methode zum Zurückgeben der letzten nicht leeren Zeile in einer Spalte für einen Datensatz.
Diese Methode funktioniert unabhängig von leeren Bereichen innerhalb des Datensatzes.
Vorsicht ist jedoch geboten , wenn verbundene Zellen beteiligt sind , da die End
Methode für einen verbundenen Bereich "angehalten" wird und die erste Zelle des zusammengeführten Bereichs zurückgibt.
Darüber hinaus werden nicht leere Zellen in ausgeblendeten Zeilen nicht berücksichtigt.
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
Um die oben genannten Einschränkungen zu beheben, gilt folgende Zeile:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
kann ersetzt werden durch:
für die zuletzt verwendete Reihe von
"Sheet1"
:
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count
.für die letzte nicht leere Zelle der Spalte
"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
Letzte Zeile mit benanntem Bereich suchen
Falls Sie einen benannten Bereich in Ihrem Arbeitsblatt haben und die letzte Zeile dieses dynamischen benannten Bereichs dynamisch abrufen möchten. Behandelt auch Fälle, in denen der benannte Bereich nicht bei der ersten Reihe beginnt.
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
Aktualisieren:
@Jeeped hat auf ein mögliches Schlupfloch für einen benannten Bereich mit nicht zusammenhängenden Zeilen hingewiesen, da dies zu einem unerwarteten Ergebnis führt. Um dieses Problem zu beheben, wird der Code wie folgt überarbeitet.
Annahmen: targes sheet = form
, benannter Bereich = 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
Holen Sie sich die Zeile der letzten Zelle in einem Bereich
'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
Suchen Sie die letzte nicht leere Spalte im Arbeitsblatt
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
Sie können zwischen zwei Optionen wählen, ob Sie wissen möchten, ob das Arbeitsblatt keine Daten enthält:
- NO: Use LastCol_1: Sie können es direkt in
wS.Cells(...,LastCol_1(wS))
- JA: Verwenden Sie LastCol_0: Sie müssen vor der Verwendung prüfen, ob das Ergebnis der Funktion 0 ist oder nicht
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
Die Eigenschaften des Err-Objekts werden beim Beenden der Funktion automatisch auf Null zurückgesetzt.
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
Letzte Zelle in Range.CurrentRegion
Range.CurrentRegion
ist ein rechteckiger Bereich, der von leeren Zellen umgeben ist. Leere Zellen mit Formeln wie =""
oder '
nicht leer betrachtet (auch durch die ISBLANK
Excel - Funktion).
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)
Suchen Sie die letzte nicht leere Zeile im Arbeitsblatt
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
Sie können zwischen zwei Optionen wählen, ob Sie wissen möchten, ob das Arbeitsblatt keine Daten enthält:
- NEIN: LastRow_1 verwenden: Sie können es direkt in
wS.Cells(LastRow_1(wS),...)
- JA: Verwenden Sie LastRow_0: Sie müssen vor der Verwendung prüfen, ob das Ergebnis der Funktion 0 ist oder nicht
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
Finden Sie die letzte nicht leere Zelle in einer Reihe
In diesem Beispiel betrachten wir eine Methode zum Zurückgeben der letzten nicht leeren Spalte in einer Zeile.
Diese Methode funktioniert unabhängig von leeren Bereichen innerhalb des Datensatzes.
Vorsicht ist jedoch geboten , wenn verbundene Zellen beteiligt sind , da die End
Methode für einen verbundenen Bereich "angehalten" wird und die erste Zelle des zusammengeführten Bereichs zurückgibt.
Darüber hinaus werden nicht leere Zellen in ausgeblendeten Spalten nicht berücksichtigt.
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
Letzte nicht leere Zelle im Arbeitsblatt suchen - Leistung (Array)
- Die erste Funktion mit einem Array ist viel schneller
- Wird der Parameter ohne den optionalen Parameter aufgerufen, wird standardmäßig
.ThisWorkbook.ActiveSheet
- Wenn der Bereich leer ist, wird
Cell( 1, 1 )
als Standard stattNothing
Geschwindigkeit:
GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds
Gemessen mit 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