SpecialCells(xlCellTypeLastCell) lügt m.E.

Forumthread: SpecialCells(xlCellTypeLastCell) lügt m.E.

Reinhard

Hallo Wissende,
in einem Blatt sind in Zeilen 1 und 2 Überschriften.
In Zeile 19 und 20 sind Summenformeln und so'n Kram.
In A3 bis A18 stehen fortlaufende Nummern.
Ich möchte nun die unterste irgendwo befüllte Zeile in B3:H18 ermitteln.
Specialcells liefert das falsche Ergebnis. Siehe Prozedur Falsch.
Indem ich alle Zellen in dem Bereich abklappere liefert mir die Prozedur Richtig das korrekte Ergebnis.
Einzelne Zellen eines Bereiches abzuklappern kostet aber Zeit wenn der Zellbereich sehr groß ist.
Dann hätte ich als einzige Idee die ich dazu habe in einer Hilfsspalte per Vba =Anzahl() bzw. Anzahl2() reinschreiben lassen und das dann per Vba ausgewertet.
Benutze ich SpecialCells falsch? Oder gibt es mir unbekannte oder mir nicht eingefallene Möglichkeiten
in einer CodeZeile die unterste belegte Zeile eines Bereichs zu ermitteln, wenn unterhalb des Bereiches noch weitere gefüllte Zellen sind?
Hier eine Beispieldatei dazu: https://www.herber.de/bbs/user/76846.xls
PS: einem nacktem Mann in die Tasche zu greifen ist schwierig. Griechenland soll kein Geld haben sagt man. Das haben wohl die nicht gewußt die eine griechische Bank am Athener Flughafen wohl am So um 1 Million (unsrer?) Euros erleichtert haben.
Merke, Unkenntnis schützt vor plötzlichem Reichtum nicht *grins*
Danke ^Gruß
Reinhard


Option Explicit
Sub Falsch()
Dim Zei As Long
Zei = Range("B3:H18").SpecialCells(xlCellTypeLastCell).Row
MsgBox Zei
End Sub
Sub Richtig()
Dim Zelle As Range, Zei As Long
For Each Zelle In Range("B3:H18")
If Zelle.Value  "" Then
If Zelle.Row > Zei Then
Zei = Zelle.Row
End If
End If
Next Zelle
MsgBox Zei
End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: SpecialCells(xlCellTypeLastCell) lügt m.E.

03.10.2011 18:37:18

fcs

Hallo Reinhard,
SpecialCells(xlTypeLastCell) ermittelt die letzte Zelle des im Tabellenblatt verwendeten Bereichs, der nicht ausgeblendet ist. Dabei können die Zellen auch leer sein, aber formatiert.
Wenn du schnell die letzte verwendete Zeile mit Inhalt in einem Bereich ermitteln willst, dann mit der folgenden benutzerdefinierten Funktion.
Die Function muss du in einem allgemeinen Modul deines VBA-Projekts einfügen und kannst sie dann beliebig im VBA-Projekt verwenden.
Gruß
Franz


Sub Richtig1()
Dim zei As Long
zei = fncLastCellWithContent(Range("B3:H18")).Row
MsgBox "letzte verwendete Zeile in Bereich ""B3:H18"": " & zei, , "Richtig1"
zei = fncLastCellWithContent(Cells).Row
MsgBox "letzte verwendete Zeile im Blatt: " & zei, , "Richtig1"
End Sub
Public Function fncLastCellWithContent(ByVal rngBereich As Range) As Range
'Gibt die letzte Zelle mit Inhalt im Bereich als Range-Objekt zurück
Dim rngZelle  As Range
With rngBereich
Set rngZelle = .Find(What:="*", After:=.Range("A1"), lookat:=xlWhole, _
LookIn:=xlFormulas, searchdirection:=xlPrevious)
If rngZelle Is Nothing Then
Set fncLastCellWithContent = .Range("A1")
Else
Set fncLastCellWithContent = rngZelle
End If
End With
End Function

Danke dir und Nachfrage

03.10.2011 19:41:31

Reinhard

Hallo Franz,
ja so funktioiniert es. An der Beispielmappe habe ich es mit folgendem Code getestet:


Sub tt()
MsgBox fncLastCellWithContent(Range("B3:H18")).Row
End Sub
Public Function fncLastCellWithContent(ByVal rngBereich As Range) As Range
'Gibt die letzte Zelle mit Inhalt im Bereich als Range-Objekt zurück
Dim rngZelle  As Range
With rngBereich
Set rngZelle = .Find(What:="*", After:=.Range("A1"), lookat:=xlWhole, _
LookIn:=xlFormulas, searchdirection:=xlPrevious)
If rngZelle Is Nothing Then
Set fncLastCellWithContent = .Range("A1")
Else
Set fncLastCellWithContent = rngZelle
End If
End With
End Function

Was ich nicht verstehe ist, im Gegensatz zur Beiapielmappe habe ich nicht nur in C11 einen Wert stehen sondern auch in Zellen oberhalb davon.
Trotzdem (was ja für meine Zwecke sehr schön ist) wird Zeile 11 als letzte Zeile ermittelt.
Warum, wieso?
Laut Hilfe und meinem Wissen liefert doch "Find" die erste Zelle im Suchbereich auf die "*" zutrifft.
Die "A1" im Code verstehe ich, das ist dann bezogen auf den Suchbereich dann in Wahrheit B3.
Auch sonst gibt mir
Set rngZelle = .Find(What:="*", After:=.Range("A1"), lookat:=xlWhole, _
LookIn:=xlFormulas, searchdirection:=xlPrevious)
keinen Hinweis darauf daß dadurch die letzte Zelle im Bereich ermittelt wird.
Oder aber, fällt mir grad beim Schreiben auf, sucht dann Find beginnend bei der Zelle vor A1 (durch xlPrevious) dann halt H18 rückwärts bis es "*" in einer Zelle findet?
Gruß
Reinhard

AW: Danke dir und Nachfrage

04.10.2011 06:49:13

Gerd

Hallo Reinhard,
richtig, vor der ersten Zelle des Bereichs liegt hier bei der Rückwärtssuche die letzte Zelle.
Die Searchorder kannst noch festlegen.
Gruß Gerd

wieder was gelernt, merci o.w.T.

04.10.2011 10:50:48

Reinhard

Infobox / Tutorial

Bestimmung der letzten belegten Zelle in Excel mit VBA

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um die unterste belegte Zeile in einem bestimmten Bereich in Excel zu ermitteln, kannst du die SpecialCells(xlCellTypeLastCell) Methode verwenden. Allerdings gibt es Berichte, dass diese Methode manchmal ungenaue Ergebnisse liefert. Hier ist ein alternativer Ansatz, den du in deinem VBA-Projekt verwenden kannst:

Öffne den VBA-Editor, indem du ALT + F11 drückst.
Füge ein neues Modul ein: Rechtsklick auf "VBAProject (deineDatei)", dann Einfügen > Modul.
Kopiere den folgenden Code in das Modul:

Public Function fncLastCellWithContent(ByVal rngBereich As Range) As Range
    ' Gibt die letzte Zelle mit Inhalt im Bereich als Range-Objekt zurück
    Dim rngZelle As Range
    With rngBereich
        Set rngZelle = .Find(What:="*", After:=.Range("A1"), lookat:=xlWhole, _
                             LookIn:=xlFormulas, searchdirection:=xlPrevious)
        If rngZelle Is Nothing Then
            Set fncLastCellWithContent = .Range("A1")
        Else
            Set fncLastCellWithContent = rngZelle
        End If
    End With
End Function

Um die letzte belegte Zeile zu ermitteln, kannst du die Funktion wie folgt verwenden:

Sub ZeileErmitteln()
    Dim letzteZeile As Long
    letzteZeile = fncLastCellWithContent(Range("B3:H18")).Row
    MsgBox "Die letzte belegte Zeile ist: " & letzteZeile
End Sub

Führe das Makro ZeileErmitteln aus, um die letzte belegte Zeile in deinem definierten Bereich zu sehen.

Häufige Fehler und Lösungen

Fehler: Runtime Error 1004: Unable to get the Find property of the Range class
- Lösung: Dieser Fehler kann auftreten, wenn der Suchbereich leer ist. Stelle sicher, dass der Bereich, den du überprüfst, tatsächlich Zellen mit Inhalt enthält.
Fehler: SpecialCells(xlCellTypeLastCell) gibt eine falsche Zeile zurück.
- Lösung: Beachte, dass SpecialCells die letzte formatierte Zelle im verwendeten Bereich ermittelt, was nicht unbedingt die letzte belegte Zelle ist. Nutze stattdessen die benutzerdefinierte Funktion, um die tatsächliche letzte belegte Zeile zu finden.

Alternative Methoden

Neben der Verwendung von SpecialCells(xlCellTypeLastCell) gibt es auch andere Möglichkeiten, die letzte belegte Zelle zu ermitteln:

Verwendung von UsedRange:

Dim letzteZeile As Long
letzteZeile = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Direktes Abfragen der Zeilen: Du kannst auch durch die Zeilen iterieren, um die letzte belegte Zeile zu finden, was jedoch zeitaufwändiger sein kann.

Praktische Beispiele

Hier sind einige praktische Beispiele, um die letzte belegte Zelle zu ermitteln:

Einfaches Makro:

Sub LetzteZelleImBlatt()
   Dim letzteZeile As Long
   letzteZeile = fncLastCellWithContent(Cells).Row
   MsgBox "Die letzte belegte Zeile im Blatt ist: " & letzteZeile
End Sub

Mit einer spezifischen Tabelle:

Sub LetzteZelleInTabelle()
   Dim letzteZeile As Long
   letzteZeile = fncLastCellWithContent(Sheets("Tabelle1").Range("A1:H20")).Row
   MsgBox "Die letzte belegte Zeile in Tabelle1 ist: " & letzteZeile
End Sub

Tipps für Profis

Optimierung: Wenn du regelmäßig große Datenmengen analysierst, kann die Verwendung von Find erheblich schneller sein als das Durchlaufen jeder Zelle in einem Bereich.
Kombinationen: Nutze die UsedRange-Eigenschaft in Kombination mit SpecialCells, um präzisere Ergebnisse zu erzielen.
Verwendung von With-Anweisungen: Um die Code-Lesbarkeit zu verbessern, verwende With-Anweisungen, wenn du mehrere Eigenschaften eines Objekts bearbeitest.

FAQ: Häufige Fragen

1. Warum liefert SpecialCells(xlCellTypeLastCell) manchmal falsche Ergebnisse?
SpecialCells(xlCellTypeLastCell) ermittelt die letzte formatierte Zelle, nicht die letzte Zelle mit Inhalt. Daher kann es in Bereichen mit leeren, aber formatierten Zellen zu Verwirrung kommen.

2. Wie kann ich die letzte belegte Zelle in einem bestimmten Bereich finden?
Verwende die benutzerdefinierte Funktion fncLastCellWithContent, um die letzte Zelle mit Inhalt in einem bestimmten Bereich zu ermitteln.

3. Funktioniert dies in allen Excel-Versionen?
Ja, die vorgestellten VBA-Methoden und Funktionen sind in den meisten modernen Excel-Versionen anwendbar, einschließlich Excel 2010 und neuer.

Beliebteste Forumthreads (12 Monate)

SpecialCells(xlCellTypeLastCell) lügt m.E.

Forumthread: SpecialCells(xlCellTypeLastCell) lügt m.E.

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Bestimmung der letzten belegten Zelle in Excel mit VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

SpecialCells(xlCellTypeLastCell) lügt m.E.

Forumthread: SpecialCells(xlCellTypeLastCell) lügt m.E.

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Bestimmung der letzten belegten Zelle in Excel mit VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread