Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Letzte benutzte Zelle ermitteln
zurück: Wie spreche ich eine Tabelle an weiter: Tabelle kopieren und Code löschen Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Information Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
Isabelle :-)
Menschin


Verfasst am:
03. Jun 2013, 21:56
Rufname:
Wohnort: Westlicher Spiralarm der Galaxis


Letzte benutzte Zelle ermitteln - Letzte benutzte Zelle ermitteln

Nach oben
       Version: Office 2k (2000)

Hallöchen,

Häufig benötigen wir für Endwerte von Schleifen, oder um die Daten in ein Array zu schreiben die letzte gefüllte Spalte / Zeile einer Tabelle.

Ein paar Beispiele wie du die letzte benutzte Zelle in einer Zeile, einer Spalte oder der gesamten Tabelle finden kannst.

Spalte A welche ausschließlich manuell gefüllt wird.

Code:
Public Sub Test1()
    Dim lngLastUsedRow As Long
    With Tabelle1
        lngLastUsedRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
End Sub

Spalte A welche per Formel gefüllt wird. Hier haben wir das Problem, dass End(xlUp) natürlich die letzte Formel findet und nicht die Zelle in welcher sich der letzte sichtbare Wert befindet.

Code:
Public Sub Test2()
    Dim objCell As Range
    Dim lngLastUsedRow As Long
    With Tabelle1.Columns(1)
        Set objCell = .Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole,  _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        lngLastUsedRow = objCell.Row
    End With
End Sub



Zeile 1 welche ausschließlich manuell gefüllt wird.

Code:
Public Sub Test3()
    Dim lngLastUsedColumn As Long
    With Tabelle1
        lngLastUsedColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
End Sub

In Zeile 1 welche per Formel gefüllt wird. Hier haben wir wie bei der Spalte das Problem, dass End(xlToLeft) natürlich die letzte Formel findet und nicht die Zelle in welcher der letzte sichtbare Wert befindet.

Code:
Public Sub Test4()
    Dim objCell As Range
    Dim lngLastUsedColumn As Long
    With Tabelle1.Rows(1)
        Set objCell = .Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole,  _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        lngLastUsedColumn = objCell.Column
    End With
End Sub



Die letzte benutzte Zelle in einer Tabelle:

Das ist nicht ganz so einfach wie bei einzelnen Spalten oder Zeilen. Hier müssen wir erst mal den benutzen Bereich ermitteln.

Jetzt könnte man meinen, einfach so:

Code:
Public Sub Test5()
    Dim lngLastUsedRow As Long
    Dim lngLastUsedColumn As Long
    With Tabelle1.UsedRange
        lngLastUsedRow = .Rows.Count
        lngLastUsedColumn = .Columns.Count
    End With
End Sub

Leider falsch gemeint. Teste das mal in einer Tabelle in der du in Zelle B2 ein x einträgst.

Du bekommst als letzte Zeile und Spalte jeweils eine 1, das wäre aber die Zelle A1.

Wir müssen also auch noch den Startpunkt der benutzten Zeilen und Spalten dazurechnen.

Also so:

Code:
Public Sub Test6()
    Dim lngLastUsedRow As Long
    Dim lngLastUsedColumn As Long
    With Tabelle1.UsedRange
        lngLastUsedRow = .Rows.Count + .Row - 1
        lngLastUsedColumn = .Columns.Count + .Column - 1
    End With
End Sub

Minus 1 deshalb, weil der Startpunkt der Zeilen und Spalten innerhalb des benutzten Bereiches befinden.

Eine andere Möglichkeit ist, das Ganze über die SpecialCells-Methode zu machen:

Code:
Public Sub Test7()
    Dim lngLastUsedRow As Long
    Dim lngLastUsedColumn As Long
    With Tabelle1.Cells.SpecialCells(xlCellTypeLastCell)
        lngLastUsedRow = .Row
        lngLastUsedColumn = .Column
    End With
End Sub

Bei beiden Varianten haben wir aber immer das Problem mit Spalten oder Zeilen welche Formeln enthalten. Sind die Formeln sehr viel weiter nach unten oder nach rechts gezogen wie die eigentlich gefüllten Zellen, dann bekommen wir von beiden einen viel zu großen Bereich zurück.

In diesem Fall müssen wir wieder mit der Find-Methode arbeiten.

Code:
Public Sub Test8()
    Dim objCell As Range
    Dim lngLastUsedRow As Long
    Dim lngLastUsedColumn As Long
    With Tabelle1.Cells
        Set objCell = .Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        lngLastUsedColumn = objCell.Column
        Set objCell = .Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        lngLastUsedRow = objCell.Row
    End With
End Sub


Aufgrund dieser Erkenntnisse können wir uns nun eine allgemeine Funktion entwickeln welche uns die gewünschte Zeile und Spalte zurückgibt. Über die beiden optionalen Parameter können wir die Suche nach der letzten Zeile oder Spalte unterdrücken. Standard ist beide.

Code:
Public Function GetLastZell( _
    ByRef probjRange As Range, _
    ByRef prlngLastRow As Long, _
    ByRef prlngLastColumn As Long, _
    Optional ByVal povblnReturnLastRow As Boolean = True, _
    Optional ByVal povblnReturnLastColumn As Boolean = True) As Boolean

    Dim objCell As Range
    Dim dblCellsCount As Double

    'Versionsabhaengig die Anzahl der Zellen im Bereich lesen
    If Val(Application.Version) > 11 Then
        dblCellsCount = probjRange.Cells.CountLarge
    Else
        dblCellsCount = probjRange.Cells.Count
    End If

    'Pruefen ob der gesamte Bereich nicht leer ist
    If WorksheetFunction.CountBlank(probjRange) <> dblCellsCount Then

        With probjRange

            If povblnReturnLastRow Then

                Set objCell = .Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole, _
                    SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
                prlngLastRow = objCell.Row

                GetLastZell = True

            End If

            If povblnReturnLastColumn Then

                Set objCell = .Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole, _
                    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
                prlngLastColumn = objCell.Column

                GetLastZell = True

            End If
        End With

        Set objCell = Nothing

    End If
End Function

Der Aufruf um die letzte benutzte Zeile und Spalte eines ganzen Blattes von der Funktion zurückzubekommen lautet so:

Code:
Public Sub Test9()
    Dim lngLastUsedRow As Long
    Dim lngLastUsedColumn As Long
    If GetLastZell(Tabelle1.Cells, lngLastUsedRow, lngLastUsedColumn) Then
        Call MsgBox("Letzte benutzte Zeile: " & CStr(lngLastUsedRow), vbInformation, "Information")
        Call MsgBox("Letzte benutzte Spalte: " & CStr(lngLastUsedColumn), vbInformation, "Information")
    Else
        Call MsgBox("Keine Zellen gefunden.", vbExclamation, "Hinweis")
    End If
End Sub


Und so der um die letzte benutzte Zeile von Spalte A:C zurückzubekommen, die Suche nach der Spalte wird unterdrückt:

Code:
Public Sub Test10()
    Dim lngLastUsedRow As Long
    If GetLastZell(Tabelle1.Columns("A:C"), lngLastUsedRow, 0, , False) Then
        Call MsgBox("Letzte benutzte Zeile: " & CStr(lngLastUsedRow), vbInformation, "Information")
    Else
        Call MsgBox("Keine Zellen gefunden.", vbExclamation, "Hinweis")
    End If
End Sub


Nachtrag: Die beschriebenen Methoden funktionieren nur dann korrekt, wenn in der Tabelle keine Zeilen / Spalten ausgeblendet sind. Wurden Spalten / Zeilen manuell oder per Gruppierung ausgeblendet, dann funktioniert die Find-Methode wenn du dem LookIn – Parameter an Stelle der Konstante xlValues die Konstante xlFormulas übergibst. Allerdings werden dann wieder Zellen mit Formeln berücksichtig welche "" zurückgeben wenn die Bedingung nicht erfüllt ist. Wurden Zeilen per Autofilter ausgeblendet, so gibt es keine Möglichkeit außer den Autofilter aufzuheben. Danke Phelan XLPH für diesen Hinweis.

_________________
LG Isi

Die Mitgliedschaft im Forum erhöht deine Chance auf eine Antwort von mir um 99,999%
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Excel Formeln: Worte aus einer Zelle in 2 Zellen schreiben 2 Sven79 1322 12. Jan 2005, 01:20
Sven79 Worte aus einer Zelle in 2 Zellen schreiben
Keine neuen Beiträge Excel Formeln: wie kann ich in einem Bezug auf eine Zelle rechnen??? 3 Maik_25 944 03. Jan 2005, 12:45
Patrick G. wie kann ich in einem Bezug auf eine Zelle rechnen???
Keine neuen Beiträge Excel Formeln: Zelle Verschieben 2 Muran 628 02. Jan 2005, 23:04
Patrick G. Zelle Verschieben
Keine neuen Beiträge Excel Formeln: dateiname in eine zelle 6 natorius 1946 03. Dez 2004, 16:25
natorius dateiname in eine zelle
Keine neuen Beiträge Excel Formeln: Bezug auf Zelle -Ausgabe Formel 12 Patrick0705 2568 26. Nov 2004, 11:36
Arnim Bezug auf Zelle -Ausgabe Formel
Keine neuen Beiträge Excel Formeln: Teile aus einer Zelle übernehmen 5 Yakima 1980 23. Nov 2004, 17:06
Yakima Teile aus einer Zelle übernehmen
Keine neuen Beiträge Excel Formeln: Zelle farbig markieren 12 Gast 4192 05. Nov 2004, 01:25
Arnim Zelle farbig markieren
Keine neuen Beiträge Excel Formeln: Letzten drei Ziffern aus einer Zelle filtern?? 7 Ben 3398 23. Sep 2004, 13:39
Gast Letzten drei Ziffern aus einer Zelle filtern??
Keine neuen Beiträge Excel Formeln: Autom. Farbänderung einer Zelle. 1 Stefan 66 5197 19. Aug 2004, 14:51
fl618 Autom. Farbänderung einer Zelle.
Keine neuen Beiträge Excel Formeln: Auslesen mehrer Zahlen aus einer Zelle 7 coatin27 3379 11. Aug 2004, 15:18
Arnim Auslesen mehrer Zahlen aus einer Zelle
Keine neuen Beiträge Excel Formeln: Eigabe nur in einer Zelle zulassen 1 pkegelking 1645 11. Aug 2004, 10:42
icke Eigabe nur in einer Zelle zulassen
Keine neuen Beiträge Excel Formeln: Löschen des Zellinhaltes/von anderer Zelle 2 Gast 1962 07. Aug 2004, 23:32
Gast Löschen des Zellinhaltes/von anderer Zelle
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: HTML Editoren Forum