Fehler beim Auffinden der zuletzt verwendeten Zelle in Excel mit VBA

Hinweis: Diese Antwort wurde begründet durch diesen Kommentar . Der Zweck von UsedRange unterscheidet sich von dem, was in der obigen Antwort angegeben ist.

Um die zuletzt verwendete Zelle richtig zu finden, muss man zuerst entscheiden, was als sed gilt, und dann eine geeignete Methode auswählen . Ich stelle mir mindestens drei Bedeutungen vor:

1. Verwendet = nicht leer, d. H. Mit Daten .

2. Used = "... in Verwendung, dh der Abschnitt, der Daten oder Formatierungen enthält." Gemäß offizieller Dokumentation , dies ist das Kriterium, das Excel zum Zeitpunkt des Speicherns verwendet. Siehe auch this . Wenn man sich dessen nicht bewusst ist, kann das Kriterium zu unerwarteten Ergebnissen führen, es kann jedoch auch absichtlich ausgenutzt werden (seltener, sicher), z. B. um bestimmte Bereiche hervorzuheben oder zu drucken, die eventuell keine Daten enthalten. Und natürlich ist es wünschenswert, dass der Bereich beim Speichern einer Arbeitsmappe als Kriterium verwendet wird, um nicht einen Teil der Arbeit zu verlieren.

3. Used = "... in use, dh der Abschnitt, der Daten oder Formatierungen " oder bedingte Formatierungen enthält. Entspricht 2. Sie können aber auch Zellen einschließen, die das Ziel für eine bedingte Formatierungsregel sind.

Wie Sie die zuletzt verwendete Zelle finden, hängt davon ab, was Sie möchten (Ihr Kriterium) .

Für Kriterium 1 empfehle ich diese Antwort. Beachten Sie, dass UsedRange als unzuverlässig bezeichnet wird. Ich denke, das ist irreführend (d. H. "Unfair" gegenüber UsedRange), da UsedRange einfach nicht dazu gedacht ist, die letzte Zelle zu melden, die Daten enthält. Es sollte also in diesem Fall nicht verwendet werden, wie in dieser Antwort angegeben. Siehe auch dieser Kommentar .

Für Kriterium 2 ist UsedRange die zuverlässigste Option im Vergleich zu anderen Optionen, die ebenfalls für diese Verwendung vorgesehen sind. Es ist sogar nicht mehr erforderlich, eine Arbeitsmappe zu speichern, um sicherzustellen, dass die letzte Zelle aktualisiert wird. Ctrl+End wird vor dem Speichern in eine falsche Zelle verschoben ("Die letzte Zelle wird erst zurückgesetzt, wenn Sie das Arbeitsblatt speichern" von http://msdn.Microsoft.com/en-us/library/aa139976%28v=office) .10% 29.aspx . Es ist eine alte Referenz, aber in dieser Hinsicht gültig).

Für Kriterium 3 kenne ich keine eingebaute Methode. Kriterium 2 berücksichtigt keine bedingte Formatierung. Möglicherweise wurden Zellen basierend auf Formeln formatiert, die von UsedRange oder _ nicht erkannt werden Ctrl+End. In der Abbildung ist die letzte Zelle B3, da die Formatierung explizit darauf angewendet wurde. Zellen B6: D7 haben ein Format, das von einer bedingten Formatierungsregel abgeleitet wurde, und dies wird selbst von UsedRange nicht erkannt. Um dies zu berücksichtigen, wäre eine gewisse VBA-Programmierung erforderlich.

Zu Ihrer speziellen Frage : Was ist der Grund dafür?

Ihr Code verwendet die erste Zelle in Ihrem Bereich E4: E48 als Trampolin für Springen Abwärts mit End(xlDown).

Die "fehlerhafte" Ausgabe wird erhalten, wenn es keine nicht-leeren Zellen in Ihrem Bereich gibt, außer vielleicht der ersten. Dann sind Sie im Dunkeln springen, d. H. Im Arbeitsblatt nach unten (Sie sollten den Unterschied zwischen leer und leere Zeichenfolge beachten!).

Beachten Sie, dass:

1. Wenn Ihr Bereich nicht zusammenhängende, nicht leere Zellen enthält, führt dies ebenfalls zu einem falschen Ergebnis.

2. Wenn es nur eine nicht leere Zelle gibt, es aber nicht die erste ist, erhalten Sie mit Ihrem Code immer noch das richtige Ergebnis.

Ich habe diese One-Stop-Funktion für Bestimmen der letzten Zeile, Spalte und Zelle, sei es für Daten, formatierte (gruppierte/kommentierte/ausgeblendete) Zellen oder bedingte Formatierung erstellt.

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

Die Ergebnisse sehen folgendermaßen aus:

Für detailliertere Ergebnisse können einige Zeilen im Code nicht kommentiert werden:

Eine Einschränkung besteht darin, dass die Ergebnisse unzuverlässig werden können, wenn das Blatt Tabellen enthält. Daher habe ich beschlossen, in diesem Fall die Ausführung des Codes zu vermeiden:

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

Ein wichtiger Hinweis, den Sie bei der Verwendung der Lösung beachten sollten ...

LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

... soll sicherstellen, dass Ihre LastRow -Variable vom Typ Long ist:

Dim LastRow as Long

Andernfalls werden in bestimmten Situationen in .XLSX-Arbeitsmappen OVERFLOW-Fehler angezeigt

Dies ist meine gekapselte Funktion, die ich in verschiedenen Code-Anwendungen verwende.

Private Function FindLastRow(ws As Worksheet) As Long
    ' --------------------------------------------------------------------------------
    ' Find the last used Row on a Worksheet
    ' --------------------------------------------------------------------------------
    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        ' Search for any entry, by searching backwards by Rows.
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End Function

Ich würde zu der Antwort von Siddarth Rout hinzufügen, dass der CountA-Aufruf übersprungen werden kann, indem Find anstelle einer Zeilennummer ein Range-Objekt zurückgibt und dann das zurückgegebene Range-Objekt testet, um festzustellen, ob es sich um Nothing handelt (leeres Arbeitsblatt). .

Außerdem müsste meine Version einer LastRow-Prozedur eine Null für ein leeres Arbeitsblatt zurückgeben, damit ich weiß, dass es leer ist.

Ich frage mich, dass niemand dies erwähnt hat, aber der einfachste Weg, die letzte benutzte Zelle zu bekommen, ist:

Function GetLastCell(sh as Worksheet) As Range
    GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell)
End Function

Dies gibt im Wesentlichen dieselbe Zelle zurück, über die Sie verfügen Ctrl + End nach Auswahl von Zelle A1.

Ein Wort der Vorsicht: Excel verfolgt die am weitesten rechts unten stehende Zelle, die jemals in einem Arbeitsblatt verwendet wurde. Wenn Sie zum Beispiel etwas in B und etwas anderes in H8 eingeben und dann später den Inhalt von H8 löschen, drücken Sie Ctrl + End bringt Sie trotzdem zu H8 Zelle. Die obige Funktion hat das gleiche Verhalten.

Da es sich bei der ursprünglichen Frage um Probleme beim Auffinden der letzten Zelle handelt, liste ich in dieser Antwort die verschiedenen Möglichkeiten auf, wie Sie unerwartete Ergebnisse erzielen können ; Siehe meine Antwort auf "Wie finde ich die letzte Zeile, die Daten in der Excel-Tabelle enthält, mit einem Makro?" für meine Überlegungen zur Lösung dieses Problems.

Ich beginne mit einer Erweiterung von die Antwort von sancho.s und der Kommentar von GlennFromIowa und füge noch mehr Details hinzu:

[...] muss man erst entscheiden, was als verwendet gilt. Ich sehe mindestens 6 Bedeutungen. Zelle hat:

• 1) Daten, d. H. Eine Formel, die möglicherweise zu einem Leerwert führt;
• 2) einen Wert, d. H. Eine nicht leere Formel oder Konstante;
• 3) Formatierung;
• 4) bedingte Formatierung;
• 5) eine Form (einschließlich Kommentar), die die Zelle überlappt;
• 6) Beteiligung an einer Tabelle (Listenobjekt).

Auf welche Kombination möchten Sie testen? Einige (z. B. Tabellen) sind möglicherweise schwieriger zu testen und einige sind möglicherweise selten (z. B. eine Form außerhalb des Datenbereichs), andere können jedoch situationsabhängig variieren (z. B. Formeln mit Leerwerten).

Weitere Dinge, die Sie möglicherweise berücksichtigen möchten:

• A) Kann es versteckte Zeilen (z. B. Autofilter), leere Zellen oder leere Zeilen geben?
• B) Welche Art von Leistung ist akzeptabel?
• C) Kann das VBA-Makro die Arbeitsmappe oder die Anwendungseinstellungen in irgendeiner Weise beeinflussen?

In diesem Sinne wollen wir uns ansehen , wie die üblichen Methoden zum Abrufen der "letzten Zelle" zu unerwarteten Ergebnissen führen können:

• Der .End(xlDown) Code aus der Frage löst sich am einfachsten auf (z. B. mit einer einzelnen nicht leeren Zelle oder wenn leere Zellen vorhanden sind dazwischen ) aus den Gründen, die in die Antwort von Siddharth Rout ​​hier erklärt wurden (Suche nach "xlDown ist gleichermaßen unzuverlässig.") ????
• Jede Lösung, die auf Counting (CountA oder Cells*.Count) Oder .CurrentRegion Basiert, bricht auch ab, wenn leere Zellen oder Zeilen vorhanden sind ????
• Bei einer Lösung, bei der .End(xlUp) vom Ende einer Spalte aus rückwärts durchsucht wird, wird genau wie bei STRG + NACH OBEN nach Daten gesucht (Formeln, die einen leeren Wert ergeben, werden berücksichtigt "Daten") in sichtbaren Zeilen (daher kann die Verwendung mit aktiviertem Autofilter zu falschen Ergebnissen führen ⚠️).

  Sie müssen darauf achten, die üblichen Fallstricke zu vermeiden (für Einzelheiten verweise ich erneut auf die Antwort von Siddharth Rout ​​hier suchen Sie nach "Letzte Zeile in einer Spalte finden" Abschnitt), z. B. die letzte Zeile (Range("A65536").End(xlUp)) hartcodieren, anstatt sich auf sht.Rows.Count zu verlassen.

• .SpecialCells(xlLastCell) entspricht STRG + ENDE und gibt die unterste und die am weitesten rechts stehende Zelle des "verwendeten Bereichs" zurück. Daher gelten für diese Methode alle Einschränkungen, die sich auf den "verwendeten Bereich" beziehen, als Gut. Darüber hinaus wird der "verwendete Bereich" nur zurückgesetzt, wenn die Arbeitsmappe gespeichert und auf worksheet.UsedRange Zugegriffen wird, sodass xlLastCell möglicherweise veraltete Ergebnisse mit nicht gespeicherten Änderungen liefert (z. B. nachdem einige Zeilen gelöscht wurden). Siehe Antwort von dotNET in der Nähe .
• sht.UsedRange (Ausführlich beschrieben in die Antwort von sancho.s hier) berücksichtigt sowohl Daten als auch Formatierung (wenn auch nicht bedingte Formatierung) und setzt den "benutzten Bereich" des Arbeitsblatts zurück, der das sein kann, was Sie wollen oder nicht.

  Beachten Sie, dass ein häufiger Fehler darin besteht, .UsedRange.Rows.Count ⚠️ zu verwenden, der die Anzahl der Zeilen im verwendeten Bereich zurückgibt, nicht die letzte Zeilennummer (Sie unterscheiden sich, wenn die ersten Zeilen leer sind.) Weitere Informationen finden Sie unter Antwort des neuen Benutzers auf Wie kann ich die letzte Zeile mit Daten in der Excel-Tabelle mit einem Makro finden?

• Mit .Find Können Sie die letzte Zeile mit beliebigen Daten (einschließlich Formeln) oder einem nicht leeren Wert in einer beliebigen Spalte suchen. Sie können wählen, ob Sie an Formeln oder Werten interessiert sind, aber der Haken ist, dass es die Standardeinstellungen im Excel-Dialogfeld "Suchen" zurücksetzt, was für Ihre Benutzer sehr verwirrend sein kann . Es muss auch vorsichtig verwendet werden, siehe die Antwort von Siddharth Rout ​​hier (Abschnitt "Letzte Zeile in einem Blatt finden")
• Ausführlichere Lösungen, mit denen einzelne Cells 'in einer Schleife überprüft werden, sind im Allgemeinen langsamer als die erneute Verwendung einer Excel-Funktion (obwohl sie immer noch leistungsfähig sein kann). Sie können jedoch genau angeben, wonach Sie suchen möchten. Siehe meine Lösung basierend auf UsedRange und VBA-Arrays, um die letzte Zelle mit Daten in der angegebenen Spalte zu finden - sie verarbeitet ausgeblendete Zeilen, Filter, Leerzeichen, ändert nicht die Suchstandards und ist ziemlich performant.

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

• verwenden Sie Long anstelle von Integer, um die Zeilennummern zu speichern (um zu vermeiden, dass Overflow mit mehr als 65.000 Zeilen ausgegeben wird)
• um immer das Arbeitsblatt anzugeben, mit dem Sie arbeiten (d. h. Dim ws As Worksheet ... ws.Range(...) anstelle von Range(...))
• wenn Sie .Value (das ist ein Variant) verwenden, vermeiden Sie implizite Umwandlungen wie .Value <> "", da diese fehlschlagen, wenn die Zelle einen Fehlerwert enthält.

sub last_filled_cell()
msgbox range("a65536").end(xlup).row
end sub

msgstr "hier ist a65536 die letzte Zelle in der Spalte a dieser Code wurde auf Excel sti72003 200 getestet."

und wenn Sie seine "a1.048.576" verwenden

mein Code ist nur für Anfänger gedacht, um zu verstehen, was end (xlup) und andere zugehörige Befehle bewirken können

Diese Frage versucht jedoch, die letzte Zeile mithilfe von VBA zu finden. Ich denke, es wäre gut, eine Array-Formel für die Arbeitsblattfunktion einzufügen, da diese häufig aufgerufen wird:

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

Sie müssen die Formel ohne Klammern eingeben und dann drücken Shift + Ctrl + Enter um es zu einer Matrixformel zu machen.

Dies gibt Ihnen die Adresse der zuletzt verwendeten Zelle in der Spalte D an.

Ich suchte nach einer Möglichkeit, das nachzuahmen CTRL+Shift+EndDaher ist die dotNET-Lösung großartig, außer dass ich in Excel 2010 ein set hinzufügen muss, um einen Fehler zu vermeiden:

Function GetLastCell(sh As Worksheet) As Range
  Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell)
End Function

und wie Sie das selbst überprüfen können:

Sub test()
  Dim ws As Worksheet, r As Range
  Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set r = GetLastCell(ws)
  MsgBox r.Column & "-" & r.Row
End Sub

In den letzten 3+ Jahren habe ich die folgenden Funktionen verwendet, um die letzte Zeile und die letzte Spalte pro definierter Spalte (für Zeile) und Zeile (für Spalte) zu finden:

Letzte Spalte:

Function lastCol(Optional wsName As String, Optional rowToCheck As Long = 1) As Long

    Dim ws  As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastCol = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function

Letzte Reihe:

Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

Für den Fall des OP ist dies der Weg, um die letzte Zeile in der Spalte E zu erhalten:

Debug.Print lastRow(columnToCheck:=Range("E4:E48").Column)

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