Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: VBA-letzte Zelle finden, Datenbereich markieren

VBA-letzte Zelle finden, Datenbereich markieren
29.08.2018 16:29:39
Jürgen
Hallo zusammen,
ich habe im Netz nach einer Lösung gesucht, leider erfolglos. Deshalb möchte ich Euch um Hilfe bitten. Meine VBA-Kenntnisse sind sehr bescheiden. Es geht darum mit VBA die letzte Zelle einer Spalte z. B. "A" in einer mit ca 1500 Zeilen gefüllten Tabelle zu finden und den Datenbereich z. B. von "A54" bis A-Tabellenende (z. B. A1500) zu markieren.
Das Problem ist nur, der Beginn bzw. Ausgangspunkt in der Spalte A ist nicht statisch in einer bestimmte Zeile (z. B. A5) sondern variiert. Die Startzelle zu finden ist nicht das Problem, jedoch für mich den Spaltenbereich von dort (z. B. A54) bis Spaltenende der gefüllten Tabelle (z. B A1500) zu markieren.
Habe bisher bei festgelegtem Ausgangspunkt (z. B. A5) mit folgendem Code gearbeitet:
"For Each r In ActiveSheet.UsedRange
If r.Rows.Hidden = False Then
letzteZeile = r.Row
End If
Next
Range("A5:A" & letzteZeile).Select"
Wie könnte man bei variierendem Ausgangspunkt vorgehen? Besten Dank im voraus!
Jürgen
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA-letzte Zelle finden, Datenbereich markieren
29.08.2018 17:23:53
Hans
Hallo
sorry, der Code ist kürzer als deine Anfrage. Schau mal bitte hier ....
laZell = Cells(Rows.Count, 1).End(xlUp).Row - für Spalte A
laZell = Cells(Rows.Count, 3).End(xlUp).Row - für Spalte C
Must du ihn in einem anderen Blatt suchen geht das -ohne Sheet.Select-
laZell = Sheets(xx).Cells(Rows.Count, 1).End(xlUp).Row
Must du ihn in einer anderen Mappe suchen geht das -ohne Workbook.Select-
laZell = Workbooks(xx).Sheets(xx).Cells(Rows.Count, 1).End(xlUp).Row
Ich hoffe diese kleine VBA Erklaerung hat dir weitergeholgen.
mfg Hans
Anzeige
AW: VBA-letzte Zelle finden, Datenbereich markieren
30.08.2018 10:11:54
Jürgen
Hallo Hans,
besten Dank für die schnelle Antwort. Hab es grad getestet, leider jedoch ohne Erfolg. Der betreffende Spaltenbereich wird leider nicht markiert. Es passiert sozusagen nix. Hier kurz er Beginn meines Makros:
Sub Makro1()
Cells.Find(What:="1.3.1.", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Offset(0, -1).Select
' (hier Ausgangspunkt erreicht in der aktuellen Tabelle, dann weiter mit)
laZell = Cells(Rows.Count, 1).End(xlUp).Row
ActiveCell.FormulaR1C1 = "=IF(RC[2]""403834"",MID(RC[1],1,4),0)"
Ich will mir in Spalte A Kennungen setzen, um dann in andern Spalten mit "Summewenn" Additionen durchführen zu können. Muss ich eventuell noch zu Beginn des Codes irgendwelche Definitionen setzen?
Ist es auch wichtig wenn Leerezeilen an einigen Stellen enthalten sind? Die sollten ignoriert werden bis der letzte Datensatz erreicht ist.
VG
Jürgen
Anzeige
AW: VBA-letzte Zelle finden, Datenbereich markieren
30.08.2018 12:15:00
Hans
Hallo Jürgen
ich kann dir nur begrenzt weiterhelfen, weil ich einige Teile deiner Aufgabe nicht verstanden habe.
Hier mal eine geaenderte Makro Version -Find ohne Select-!! Ist über eine Set Anweisung eleganter.
Grosser Vorteil, ich gebe den Suchwert immer in eine Variable ein, sonst muss man das Makro in Cells.Find immer auf andere Werte umschreiben!! Solltest du auch beim normalen Suchlauf mit .Activate über Variable machen. Den Start ab wo gesucht wırd kann man in After: festlegen. z.b. mit Range("xx")
Zwei Dinge habe ich noch nicht verstanden, wozu du LastZell überhaupt brauchst? Das ist im Code nicht ersichtlich.
Und brauchst du LastZell von Spalte A, oder in der gefundenen Spalte?
Was meinst du genau mit Kennzeichnung in Spalte A? Das verstehe ich im Augenblick noch nicht.
Was ist mit der Formel? Soll die nur in einer Zelle oder in mehreren Zellen gesetzt werden?
Bei der Set-Anweisung must du unbedingt mit If Not is Nothing arbeiten um Laufzeitfehler zu vermeiden!
mfg Hans
Sub Test()
Dim rFind As Range   'Find Adresse  (als Set)
Dim sWert As String  'Such-Wert  (variabel!!)
sWert = "1.3.1."   'so ist der Suchwert variabel!!
Set rFind = Cells.Find(What:=sWert, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If rFind Is Nothing Then MsgBox sWert & " nicht gefunden": Exit Sub
If Not rFind Is Nothing Then
'LastZell in der gefundenen Spalte durchführen!!
lz = Cells(Rows.Count, rFind.Column).End(xlUp).Row
rFind.Offset(0, -1).FormulaR1C1 = "=IF(RC[2]""403834"",MID(RC[1],1,4),0)"
'** nur zum Test ob Find Makro richtig funktioniert
rFind.Offset(0, -1).Select
MsgBox lz   'LastZell anzeigen
'** diesen Teil nachher löschen, dient nur zum Test!!
End If
End Sub

Anzeige
AW: VBA-letzte Zelle finden, Datenbereich markieren
31.08.2018 13:23:56
Jürgen
Hallo Hans,
ich würde sagen, 50% sind geschafft. Der richtige Ausgangspunkt wird gefunden und in die Zelle A5 die ersten vier Zeichen aus B5 übertragen (s. Bild). Funktioniert! Diesen Vorgang des Übernehmens der ersten 4 Zeichen aus Spalte B in Spalte A zeilenweise sollte das Makro bis ans Ende der Tabelle fortführen. Das wären dann die 2. 50%. Im Bild wäre das bis Zeile 14. Könntest Du das noch bewerkstelligen? Bis dahin schon mal herzlichen Dank.
Diese ganze Prozedur muss ich für weitere Punkte z. B. 1.4.1.9, 1.12.2.5.7. usw. durchführen, um wie gesagt in anderen Spalten der Tabelle Zwischensummen unter Berücksichtigung dieser gesetzten Merkmale in Spalte A bilden zu können.
Ich hoffe, ich konnte mein Ansinnen halbwegs verständlich rüberbringen.
Die Anzahl der Zeilen der Tabelle wird übrigens korrekt angegeben.
Userbild
VG
Jürgen
Anzeige
AW: VBA-letzte Zelle finden, Datenbereich markieren
31.08.2018 15:52:42
Hans
Hallo Jürgen,
kannst du mir eine kleine Beispieledatei mit 20-30 Zeilen hochladen aus denen ich alle Werte ersehen kann die gesucht und kopiert werden müssen. Sowohl die Zieltabelle, mit Lösung, wie dıe Quelldaten. Die kannst du mit Fanatasie Daten anonymisieren. Ich sehe dann besser was ich programmieren muss.
mfg Hans
Anzeige
AW: VBA-letzte Zelle finden, Datenbereich markieren
31.08.2018 15:56:55
Hans
Nachtrag
ich sehe in deiner Tabelle in Zeile 14 einen gelb markierten Wert "403834"
Was hat es damit auf sich, muss das berücksichtigt werden?
mfg Hans
AW: VBA-letzte Zelle finden, Datenbereich markieren
31.08.2018 22:40:52
Jürgen
Hallo Hans,
anbei ein Miniausschnitt aus der Tabelle. Ziel ist das Einfügen der Zwischensummen wie in der Tabelle fett dargestellt. Problematisch für mich ist das Füllen der Spalte A. Dein Makro findet in B8 "1.3.1." und setzt in A8 dann die ersten 4 Zeichen aus B8 nämlich "1.3.". Gut! Dieses Setzen der ersten 4 Zeichen sollte in Spalte A dann komplett bis Tabellenende also hier A23 geschehen! Danach erfolgt das Setzen der Summenformeln in den Spalten P und T, ist aber kein Problem. Das kann ich dann wieder. Danach geht es analog weiter mit "1.4.2." wo etwas mehr Zeichen in die Zellen von A übernommen werden usw.
Der gelb markierte Wert "403834" wird beim Setzen von "1.3." durch die Formelbedingung in Spalte A ausgeschlossen (Sonderfall). Es bleibt also alles in ein und derselben Tabelle. Ich werde sie monatlich mit neuen Datensätzen füllen. Es werden immer ein paar neue Zeilen hinzukommen, wie viele und an welcher Stelle ist ungewiss. Ich habe alle Formeln drin gelassen zum besseren Verständnis.
VG Jürgen
https://www.herber.de/bbs/user/123680.xlsx
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

VBA: Letzte Zelle finden und Datenbereich markieren in Excel


Schritt-für-Schritt-Anleitung

  1. VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor in Excel zu öffnen.

  2. Neues Modul erstellen: Klicke mit der rechten Maustaste auf „VBAProject (dein Dokument)“ und wähle „Einfügen“ > „Modul“.

  3. Code eingeben: Kopiere den folgenden Code in das Modul:

    Sub MarkiereBisTabellenende()
       Dim letzteZeile As Long
       Dim startZelle As Range
    
       ' Ausgangspunkt festlegen (hier kann die Zelle variieren)
       Set startZelle = Cells.Find(What:="1.3.1.", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart)
    
       If Not startZelle Is Nothing Then
           ' Letzte Zeile der Spalte A finden
           letzteZeile = Cells(Rows.Count, startZelle.Column).End(xlUp).Row
    
           ' Bereich von der Startzelle bis zur letzten Zelle markieren
           Range(startZelle, Cells(letzteZeile, startZelle.Column)).Select
       Else
           MsgBox "Startzelle nicht gefunden"
       End If
    End Sub
  4. Makro ausführen: Schließe den VBA-Editor und gehe zurück zu Excel. Drücke ALT + F8, wähle „MarkiereBisTabellenende“ und klicke auf „Ausführen“.


Häufige Fehler und Lösungen

  • Fehler: "Startzelle nicht gefunden": Stelle sicher, dass der Suchwert in der Tabelle vorhanden ist. Ansonsten wird die Variable startZelle als Nothing betrachtet.

  • Problem beim Markieren: Wenn der Bereich nicht korrekt markiert wird, überprüfe, ob die Variablen und die Logik im Code richtig gesetzt sind. Nutze MsgBox, um Zwischenergebnisse anzuzeigen.

  • Leere Zeilen ignorieren: Wenn leere Zeilen in der Tabelle enthalten sind, wird der Code dennoch bis zur letzten gefüllten Zelle in der Spalte A arbeiten.


Alternative Methoden

  • Verwendung von Excel-Formeln: Du kannst auch die Funktion LETZTE in Excel verwenden, um die letzte Zelle einer Spalte zu finden, jedoch ist der VBA-Ansatz oft flexibler, besonders bei variierenden Ausgangspunkten.

  • Automatische Markierung: Eine weitere Möglichkeit ist, den Bereich dynamisch zu definieren, indem du die CurrentRegion-Eigenschaft nutzt, um bis zum Tabellenende zu markieren.

    Range(startZelle, startZelle.CurrentRegion).Select

Praktische Beispiele

  • Beispiel 1: Wenn du die Zellen von A54 bis zur letzten gefüllten Zelle in Spalte A markieren möchtest, setze die startZelle einfach auf Range("A54").

  • Beispiel 2: Um den gesamten Datenbereich einer Tabelle zu markieren, kannst du den folgenden Code verwenden:

    Dim DatenBereich As Range
    Set DatenBereich = Range("A1").CurrentRegion
    DatenBereich.Select

Tipps für Profis

  • Zugriff auf die letzte Spalte: Wenn du die letzte Spalte einer bestimmten Zeile finden möchtest, benutze:

    Dim letzteSpalte As Long
    letzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column
  • Fehlerbehandlung hinzufügen: Es ist immer gut, eine Fehlerbehandlung in deinen VBA-Code einzubauen, um Laufzeitfehler zu vermeiden, besonders wenn du mit Benutzerinput arbeitest.

On Error Resume Next
' Dein Code hier
On Error GoTo 0

FAQ: Häufige Fragen

1. Wie kann ich die Markierung in einer anderen Spalte durchführen? Du musst die startZelle-Variable entsprechend anpassen, z.B. auf Range("B54").

2. Was passiert, wenn ich leere Zeilen in meiner Tabelle habe? Der Code ignoriert leere Zeilen und markiert bis zur letzten gefüllten Zelle.

3. Ist dieser VBA-Code in allen Excel-Versionen nutzbar? Ja, der Code sollte in allen modernen Excel-Versionen (ab Excel 2007) funktionieren.

4. Wie kann ich den Bereich ohne Verwendung von Select markieren? Du kannst den Bereich direkt mit einer Variable speichern und dort weiterarbeiten, ohne ihn sichtbar zu markieren.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige