Fehler bei der Suche nach Letzte benutzte Zelle in Excel mit VBA

HINWEIS: ich beabsichtige, machen dies zu einem "one-stop-post", wo Sie verwenden können, die Correct Weg zu finden die Letzte Zeile. Dies wird sich auch auf die besten Praktiken zu Folgen, wenn die Suche nach der letzten Zeile. Und daher werde ich aktualisieren, sobald ich über ein neues Szenario/Informationen.

Unzuverlässig Möglichkeiten zu finden, die Letzte Zeile

Einige der häufigsten Wege, suchen der letzten Zeile, die sind sehr unzuverlässig und daher nie verwendet werden soll.

1. UsedRange
2. xlDown
3. CountA

UsedRange sollte NIE werden verwendet, um die Letzte Zelle, die Daten enthält. Es ist höchst unzuverlässig. Versuchen Sie dieses experiment.

Typ etwas in Zelle A5. Wenn Sie nun die Berechnung der letzten Zeile mit einer der Methoden, die unten gegeben werden, wird es geben Sie 5. Jetzt die Farbe der Zelle A10 rot. Wenn Sie jetzt verwenden Sie eine der folgenden Codes, erhalten Sie noch 5. Wenn Sie Usedrange.Rows.Count was bekommen Sie? Es werden nicht 5.

Hier ist ein Szenario um zu zeigen, wie UsedRange funktioniert.

xlDown ist ebenso unzuverlässig.

Betrachten Sie diesen code

lastrow = Range("A1").End(xlDown).Row

Was passieren würde, wenn es nur eine Zelle ( A1 ), die Daten? Werden Sie am Ende das erreichen der letzten Zeile im Arbeitsblatt! Es ist wie die Auswahl Zelle A1 und drücken Sie dann - Taste und dann drücken von Pfeil nach Unten - Taste. Dies wird auch Ihnen unzuverlässige Ergebnisse, wenn es leere Zellen in einem Bereich.

CountA ist auch unzuverlässig, weil es Ihnen wird Falsches Ergebnis, wenn es leere Zellen dazwischen.

Und daher sollte man es vermeiden die Verwendung von UsedRange, xlDown und CountA zu finden, die Letzte Zelle.

Finden, die Letzte Zeile in einer Spalte

Finden Sie die Letzte Zeile in Col E verwenden Sie diese

With Sheets("Sheet1")
    LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With

Wenn Sie bemerken, dass wir eine . vor Rows.Count. Wir oft wählte zu ignorieren, dass. Sehen DIESE Frage nach der möglichen Fehler, die Sie bekommen kann. Ich rate immer mit . vor Rows.Count und Columns.Count. Diese Frage ist ein klassisches Szenario, wo der code fehl, da die Rows.Count zurück 65536 für Excel 2003 und früheren Versionen 1048576 für Excel 2007 und höher. Ähnlich Columns.Count zurück 256 und 16384 bzw.

Der oben genannten Tatsache, dass Excel 2007+ hat 1048576 Zeilen legt auch Wert auf die Tatsache, dass wir immer deklarieren Sie die variable, die halten Sie die Zeile Wert als Long statt Integer sonst erhalten Sie eine Overflow Fehler.

Finden, die Letzte Zeile in einem Blatt

Finden die Effective Letzte Zeile im Blatt, verwenden Sie diese. Beachten Sie die Verwendung von Application.WorksheetFunction.CountA(.Cells). Dies ist erforderlich, weil, wenn es keine Zellen mit Daten in dem Arbeitsblatt, dann .Find geben Sie Run Time Error 91: Object Variable or With block variable not set

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With

Finden, die Letzte Zeile in einer Tabelle (ListObject)

Gelten die gleichen Prinzipien, zum Beispiel, um die Letzte Zeile in der Dritten Spalte einer Tabelle:

Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1")  'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")

With tbl.ListColumns(3).Range
    lastrow = .Find(What:="*", _
                After:=.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
End With

End Sub

InformationsquelleAutor Siddharth Rout

Habe ich diese one-stop Funktion für Ermittlung der letzten Zeile, Spalte und Zelle, sei es für Daten, formatiert (zusammengefasst/kommentiert/versteckte) Zellen oder bedingte Formatierung.

Sub LastCellMsg()
    Dim strResult As String
    Dim lngDataRow As Long
    Dim lngDataCol As Long
    Dim strDataCell As String
    Dim strDataFormatRow As String
    Dim lngDataFormatCol As Long
    Dim strDataFormatCell As String
    Dim oFormatCond As FormatCondition
    Dim lngTempRow As Long
    Dim lngTempCol As Long
    Dim lngCFRow As Long
    Dim lngCFCol As Long
    Dim strCFCell As String
    Dim lngOverallRow As Long
    Dim lngOverallCol As Long
    Dim strOverallCell As String

    With ActiveSheet

        If .ListObjects.Count > 0 Then
            MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
            Exit Sub
        End If

        strResult = "Workbook name: " & .Parent.Name & vbCrLf
        strResult = strResult & "Sheet name: " & .Name & vbCrLf

        'DATA:
        'last data row
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataRow = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Row
        Else
            lngDataRow = 1
        End If
        'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf

        'last data column
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataCol = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByColumns, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Column
        Else
            lngDataCol = 1
        End If
        'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf

        'last data cell
        strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString)
        strResult = strResult & "Last data cell: " & strDataCell & vbCrLf

        'FORMATS:
        'last data/formatted/grouped/commented/hidden row
        strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0))
        'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf

        'last data/formatted/grouped/commented/hidden column
        lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column
        'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf

        'last data/formatted/grouped/commented/hidden cell
        strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString)
        strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf

        'CONDITIONAL FORMATS:
        For Each oFormatCond In .Cells.FormatConditions

            'last conditionally-formatted row
            lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0)))
            If lngTempRow > lngCFRow Then lngCFRow = lngTempRow

            'last conditionally-formatted column
            lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column
            If lngTempCol > lngCFCol Then lngCFCol = lngTempCol
        Next
        'no results are returned for Conditional Format if there is no such
        If lngCFRow <> 0 Then
            'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf
            'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf

            'last conditionally-formatted cell
            strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString)
            strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf
        End If

        'OVERALL:
        lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow)
        'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf
        lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol)
        'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf
        strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString)
        strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf

        MsgBox strResult
        Debug.Print strResult

    End With

End Sub

Ergebnisse sehen wie folgt aus:

Weitere detaillierte Ergebnisse, die einige Zeilen im code auskommentiert werden:

Eine Einschränkung vorhanden ist - wenn es Tabellen gibt, in der Blatt -, können die Ergebnisse unzuverlässig werden, so ich entschieden, um zu vermeiden, läuft der code in diesem Fall:

If .ListObjects.Count > 0 Then
    MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
    Exit Sub
End If

InformationsquelleAutor ZygD

Ich würde hinzufügen, um die Antwort Siddarth dem Router sagen, dass die CountA-Aufruf übersprungen werden kann, indem er Finden Rücksendung ein Range-Objekt, anstatt eine Zeilennummer ein, und testen Sie dann das zurückgegebene Range-Objekt zu sehen, wenn es Nichts (leeres Arbeitsblatt).

Auch, ich hätte meine version eines LastRow Prozedur eine null für ein leeres Arbeitsblatt kopiert haben, dann kann ich wissen, es ist leer.

InformationsquelleAutor no comprende

Da die original-Frage ist über Probleme mit der Suche nach der letzten Zelle, in dieser Antwort werde ich eine Liste der verschiedenen Möglichkeiten, können Sie unerwartete Ergebnisse erhalten; siehe meine Antwort an "Wie finde ich die Letzte Zeile, die Daten enthält, in das Excel-Tabellenblatt mit einem makro?" für mein nehmen auf die Lösung.

Werde ich beginnen, durch den ausbau auf die Antwort von sancho.s und der Kommentar von GlennFromIowa, indem noch mehr detail:

[...] man muss sich zunächst entscheiden, was als verwendet. Ich sehe mindestens 6 Bedeutungen. Zelle:

• 1) die Daten, d.h., eine Formel, deren Ergebnis in einem leeren Wert;
• 2) einen Wert, d.h., eine nicht-leere Formel oder Konstante, die
• 3) formatieren;
• 4) bedingte Formatierung;
• 5) einer Form (einschließlich Kommentar) überlappend in die Zelle;
• 6) Beteiligung in einer Tabelle (listobjekt).

Welche Kombination willst du testen? Einige (z.B. Tabellen) kann schwieriger sein, zu testen, und einige wenige (wie eine Form, die außerhalb des Datenbereichs), aber andere können variieren, abhängig von der situation können (z.B. Formeln mit leeren Werten).

Andere Dinge möchten Sie vielleicht zu berücksichtigen:

• A) Kann es sein ausgeblendeten Zeilen (z.B. autofilter), leere Zellen oder leere Zeilen?
• B) Welche Art von Leistung ist akzeptabel?
• C) Kann das VBA-makro Einfluss auf die Arbeitsmappe oder die Einstellungen der Anwendung in irgendeiner Weise?

Mit dem im Verstand, lassen Sie uns sehen,, wie der gemeinsame Weg die "Letzte Zelle" kann zu unerwarteten Ergebnissen:

• Die .End(xlDown) code aus der Frage zu brechen die meisten einfach (z.B. mit einem einzigen nicht-leeren Zelle oder wenn es leere Zellen zwischen) für die Gründe erläutert in die Antwort von Siddharth Rout hier (Suche nach "xlDown ist ebenso unzuverlässig.") ?
• Jede Lösung basiert auf Counting (CountA oder Cells*.Count) oder .CurrentRegion wird auch brechen in Anwesenheit von leeren Zellen oder Zeilen ?
• Eine Lösung mit .End(xlUp) die Suche rückwärts vom Ende einer Spalte, so wie bei STRG+UP, look für Daten (Formeln produzieren einen leeren Wert sind als "Daten") in sichtbaren Zeilen (so mit es mit autofilter aktiviert möglicherweise falsche Ergebnisse ⚠️).

  Müssen Sie darauf achten, dass die standard-fallen (für details werde ich wieder finden die Antwort von Siddharth Rout hier suchen Sie nach dem "Finde Letzte Zeile in einer Spalte" Abschnitt), wie hard-coding der letzten Zeile ( Range("A65536").End(xlUp) ), anstatt sich auf sht.Rows.Count.

• .SpecialCells(xlLastCell) ist äquivalent zu STRG+ENDE, wieder die unteren und am rechten Zelle der "Bandbreite", also alle Vorsichtsmaßnahmen, die für die Berufung auf die "used range", für diese Methode als gut. Neben der "Bandbreite" wird nur zurückgesetzt, wenn das speichern der Arbeitsmappe und beim Zugriff auf worksheet.UsedRange, so xlLastCell produzieren könnten, veraltete Ergebnisse⚠️ mit nicht gespeicherten änderungen (z.B. nach einigen Zeilen gelöscht wurden). Finden Sie die in der Nähe Antwort von dotNET.
• sht.UsedRange (beschrieben im detail in die Antwort von sancho.s hier) der Ansicht, sowohl Daten als auch Formatierung (wenn auch nicht die bedingte Formatierung) und setzt die "Bandbreite" des Arbeitsblatts, die möglicherweise oder möglicherweise nicht, was Sie wollen.

  Beachten Sie, dass ein häufiger Fehler ️ist die Verwendung .UsedRange.Rows.Count⚠️, gibt die Anzahl der Zeilen im verwendeten Bereich, nicht die Letzte Zeile (Sie wird anders sein, wenn die ersten paar Zeilen sind leer), für details siehe newguy Antwort auf Wie finde ich die Letzte Zeile, die Daten enthält, in das Excel-Tabellenblatt mit einem makro?

• .Find ermöglicht das finden der letzten Zeile mit Daten (einschließlich Formeln) oder ein nicht-leerer Wert in jeder Spalte. Sie können wählen, ob Sie interessiert sind, die Formeln oder Werte, aber der Haken ist, dass es werden die Standardeinstellungen, die im Excel - Dialogfeld "Suchen" ️️⚠️, das kann sehr verwirrend für die Benutzer. Es muss auch mit bedacht eingesetzt werden, siehe die Antwort von Siddharth Rout hier (Abschnitt "Finde Letzte Zeile auf Blatt")
• Mehr explizite Lösungen, prüfen die einzelnen Cells' in einer Schleife werden in der Regel langsamer als der re-mit Hilfe einer Excel-Funktion (obwohl noch performant), aber lassen Sie Sie genau angeben, was Sie wollen, zu finden. Sehen meine Lösung basierend auf UsedRange - und VBA-arrays zu finden, die Letzte Zelle mit Daten in der angegebenen Spalte-es Griffe ausgeblendete Zeilen, Filter blanks, nicht ändern, die Finden defaults und ist Recht performant.

Welche Lösung auch immer Sie wählen, seien Sie vorsichtig

• zu verwenden Long statt Integer zum speichern der Zeilennummern (um zu vermeiden, immer Overflow mit mehr als 65k Zeilen) und
• immer geben Sie das Arbeitsblatt, mit dem Sie arbeiten (d.h. Dim ws As Worksheet ... ws.Range(...) statt Range(...))
• bei der Verwendung .Value (das ist ein Variant) vermeiden Sie implizite Typumwandlungen wie .Value <> "" wie Sie scheitern wird, wenn die Zelle einen Fehlerwert.

InformationsquelleAutor Nickolay

Allerdings ist diese Frage sucht, um zu finden, die Letzte Zeile mittels VBA, ich denke, es wäre gut, um eine array Formel für die Arbeitsblatt-Funktion wie diese bekommt Häufig besucht:

{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}

Müssen Sie die Formel ohne die Klammern und drücken Sie dann Shift + Strg + Enter, um das array Formel.

Dieser wird Ihnen die Adresse der letzten benutzten Zelle in der Spalte D.

InformationsquelleAutor M-M

Sub lastRow()

    Dim i As Long
        i = Cells(Rows.Count, 1).End(xlUp).Row
            MsgBox i

End Sub

sub LastRow()

'Paste & for better understanding of the working use F8 Key to run the code .

dim WS as worksheet
dim i as long

set ws = thisworkbook("SheetName")

ws.activate

ws.range("a1").select

ws.range("a1048576").select

activecell.end(xlup).select

i= activecell.row

msgbox "My Last Row Is " & i

End sub

InformationsquelleAutor user85489