Office: [VBA] Letzte zelle (Ignorieren von Filter und ausgeblendete Zeilen)

Muecke.1982 · 28. März 2020

Hallo miteinander,

ich ermittle die Letzte Zelle in einer Externe Datei.
Das klappt auch, solange nicht die letzten Zeilen ausgeblendet sind, wie kann ich das erreichend das auch die ausgeblendeten Zeilen mit berücksichtigt werden?
ich wollte nicht erst die gesamte Spalte Kopien und in ein anderen Blatt einfügen um dann zu schauen wo die letzte Zeile sich befindet.

Da isst mein Test Code:
Code:

Dim LetzteZeile_Spalte_A As Integer
LetzteZeile_Spalte_A = Sheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print "Letzte Zelle: " & LetzteZeile_Spalte_A
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Muster: A1 bis A10 mit Inhalt gefüllt

Ausgabe: Letzte Zelle: 10

Jetzt Zeile 5 bis 12 ausgeblendet
Ausgabe: Letzte Zelle: 4

Das ist ein Problem für mich :-(
Gibt es eine Möglichkeit nicht nur auf die Sichtbaren Zellen zuzugreifen?

Gruß Mücke

:)

wb425 · 30. März 2020

Hallo Mücke,
vielleicht hilft dieser Link:
https://www.herber.de/forum/archiv/1...ermitteln.html

Muecke.1982 · 30. März 2020

- Löschen -
leider ist mir ein Fehler unterlaufen :-(
Sorry. muss da nochmal genauer drüber schauen.

Beverly · 30. März 2020

Hi,

die letzte belegte Zeile auch in gefilterten Tabellen lässt sich so ermitteln:

Code:

Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Anstelle Cells kann man auch eine Spalte angeben, um die letzte belegte Zeile nur in dieser Spalte zu ermitteln.

Die letzte blegte Spalte dann so:

Code:

Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Zu beachten ist, dass ein Fehler ausgelöst wird, wenn die Tabelle komplett leer ist. Das kann man aber umgehen, indem man vorher prüft ob Zellen belegt sind:

Code:

If Application.CountA(Cells) > 0 Then...
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Muecke.1982 · 30. März 2020

Servus Karin,

leider muss ich deiner aussage widersprechen, Sorry.

Test Code:
Code:

Option Explicit

Sub Datenbereich_ermitteln()
' @Beverly: die letzte belegte Zeile auch in gefilterten Tabellen lässt sich so ermitteln:
Debug.Print "1) Letzte belegte Zeile: "; Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
' Ausgabe ohne Filter: 10
' Ausgabe mit Filter: 9
' Filter: Zeile 10 wurde im Filter nicht angezeigt

' @Beverly: Die letzte blegte Spalte dann so:
Debug.Print "2) Letzte blegte Spalte: "; Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
' Ausgabe ohne Filter: 4
' Ausgabe mit Filter: 3
' Filter: Spalte 4 (D) ist ausgeblendet

' @Beverly: ob Zellen belegt sind
Debug.Print "3) Anzahl der belegten Zellen: "; Application.CountA(Cells)
' Ausgabe ohne Filter: 27
' Ausgabe mit Filter: 27
' Filter: Spalte 4 (D) ist ausgeblendet
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Aufbau der Tabelle:
Bereich B2 : D10 hat Text und Zahleninhalt.

Drumherum habe ich noch ein paar Formatierungen eingefügt, die sollen ja nicht erkannt werden.
Muster Datei habe ich angehängt ;-)

gruß Mücke

Muecke.1982 · 30. März 2020

Hallo Wolfgang,

ich habe den Link nun genauer angeschaut, der Befehl .UsedRange scheint ganz nett zu sein, für meinen Einsatz jedoch ungeeignet :-(.

jedoch wird hier noch auf einen ausführlicheren Code eingegangen, der hat mich zu meinen Ziel gebracht, Code weiter unten.

Leider hat sich meine Frage Stellung in zwischen etwas geändert, denn was ich nicht bedacht habe ist das die Daten nihct immer in Zelle A1 beginnen ;-(.

Daher suche ich nun noch einen Weg die erste beschriebene Spalte und Zeile zu ermitteln.

Das ist mein bisher zusammen getragener Code:
Code:

Option Explicit

Sub test()
Debug.Print "LastRow : "; LastRow(Sheets("Tabelle1"))
Debug.Print "LastCol : "; LastCol(Sheets("Tabelle1"))
End Sub

Function LastRow(wks As Worksheet) As Long
' Letzte Beschriebene Zeile ermitteln
' Dieser Code funktioniert auch bei Autofilter

Dim lngFirst As Long, lngLast As Long, lngTmp As Long

With Application
If .CountA(wks.Cells) = 0 Then Exit Function
If .CountA(wks.Rows(wks.Rows.Count)) Then
LastRow = wks.Rows.Count: Exit Function
End If
lngLast = wks.Rows.Count
Do While lngLast > lngFirst + 1
lngTmp = (lngFirst + lngLast) 2
If .CountA(wks.Rows(lngTmp).Resize(lngLast - lngTmp)) Then _
lngFirst = lngTmp Else lngLast = lngTmp
Loop
If .CountA(wks.Rows(lngLast)) Then LastRow = lngLast Else LastRow = lngFirst
End With
End Function

Function LastCol(wks As Worksheet) As Long
' Letzte Beschriebene Spalte ermitteln
' Dieser Code funktioniert auch bei Autofilter

Dim lngFirst As Long, lngLast As Long, lngTmp As Long

With Application
If .CountA(wks.Cells) = 0 Then Exit Function
If .CountA(wks.Columns(wks.Columns.Count)) Then
LastCol = wks.Columns.Count: Exit Function
End If
lngLast = wks.Columns.Count
Do While lngLast > lngFirst + 1
lngTmp = (lngFirst + lngLast) 2
If .CountA(wks.Columns(lngTmp).Resize(, lngLast - lngTmp)) Then _
lngFirst = lngTmp Else lngLast = lngTmp
Loop
If .CountA(wks.Columns(lngLast)) Then LastCol = lngLast Else LastCol = lngFirst
End With
End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

jetzt Fehlt noch Die erste Spalte und Zeile ;-), ich suche dann mal weiter.

Gruß Mücke

Beverly · 31. März 2020

Hi,

kann ich nicht nachvollziehen - siehe Anhang. Mit den Veränderungen die du im Code angegeben hast, wird Spalte 4 (D) und Zeile 10 als letzte belegte Spalte/Zeile ausgeben.

Luschi · 31. März 2020

Hallo Karin,

sobald man in Deinem Beispiel den Filter setzt und nicht nur Zeilen/Spalten ausblendet, wird die letztze belegte Zelle vom Filter dominiert, wie im Bild zu erkennen.

Gruß von Luschi
aus klein-Paris

Beverly · 31. März 2020

Hi,

bei händisch ausgeblendeten Zeilen funktioiniert mein Vorschlag, bei gefilterten Zeilen nicht. Aber folgende Variante funktioniert, wenn in einem Autofilterbereich die letzte belegte Zeile ermittelt werden soll:

Code:

MsgBox ActiveSheet.AutoFilter.Range.Rows.Count + ActiveSheet.AutoFilter.Range.Rows(1).Row - 1
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Der Code für die letzte belegte Spalte funktioniert immer, da Spalten nicht gefiltert werden können.

Office: [VBA] Letzte zelle (Ignorieren von Filter und ausgeblendete Zeilen)

[VBA] Letzte zelle (Ignorieren von Filter und ausgeblendete Zeilen)

[VBA] Letzte zelle (Ignorieren von Filter und ausgeblendete Zeilen)

[VBA] Letzte zelle (Ignorieren von Filter und ausgeblendete Zeilen)

[VBA] Letzte zelle (Ignorieren von Filter und ausgeblendete Zeilen)

[VBA] Letzte zelle (Ignorieren von Filter und ausgeblendete Zeilen) - Similar Threads - VBA Letzte zelle

VBA - letzte nicht-leere Spalte

Drucken bis letzte beschriebene Zeile per VBA

Werte der letzten Zelle einer Spalte in mehreren Tabellenblättern ermitteln

Nächste Leere Zeile statt Letzte Zeile finden

Vba letzte Spalte in einer Bestimmten Range ermitteln

letzte beschriebene Zelle ermitteln. Ohne Makros und VBA

Mal wieder letzte benutzte Zelle per VBA ermitteln