Hallo zusammen,
ich habe ein Frage,
Ich habe eine Tabelle, in der steht in Spalte A ein Wert und unter diesem Wert sind immer eine unterschiedliche Anzahl an Leerzeilen. Diese müssen nun gezählt werden und dann müssen soviele Leerzeilen eingefügt werden, dass es immer 12 Leerzeilen ergibt.
Kann man das über VBA abbilden. Ich muss diesen Vorgang öfter durchführen.
Vielen Dank im voraus…
LG Martina
Hallo Martina,
probier’s mal mit diesem Code:
Sub zeilenEinfügen()
Dim z1 As Long, z2 As Long
On Error GoTo schluss
z1 = Cells(Rows.Count, 1).End(xlUp).Row
While z1 \> 1
While Not IsEmpty(Cells(z1, 1))
z1 = z1 - 1
Wend
z2 = z1 - 1
While IsEmpty(Cells(z2, 1))
z2 = z2 - 1
Wend
If z1 - z2
Füge ihn in ein Standard-Modul ein.
Dann aktiviere das Tabellenblatt, in dem die Zeilen eingefügt werden sollen und starte den Makro.
Was er nicht berücksichtigt: Falls schon 12 oder mehr Leerzeilen vorhanden sind, werden keine Zeilen gelöscht.
Gruß, Andreas
Hallo Andreas,
das Makro funktioniert super. Vielen Dank.
Könntest Du mir das Makro noch etwas kommentieren, damit ich weiß, was und wie bei diesem Makro passiert? Z.B.: den Begriff Wend kenne ich garnicht. Ich möchte das Makro gerne verstehen, damit ich bei Bedarf selber mal so ein oder ähnliches Makro schreiben kann.
Vielen Dank schon einmal im voraus.
LG Martina
Hallo Martina,
mach ich heute Abend.
Gruß, Andreas
Hier mit Kommentaren
Hallo Martina,
hier der Makro jetzt ausführlich kommentiert:
Sub zeilenEinfügen()
' Wir brauchen 2 Zeiger auf Zeilennummern:
Dim z1 As Long, z2 As Long
' Wenn ein "Fehler" auftritt (kann weiter unten passieren),
' soll zur Marke "schluss" (identisch mit dem Ende der Routine)
' gesprungen werden:
On Error GoTo schluss
' Wenn man das Ganze von oben nach unten durchlaufen würde,
' wüsste man nie genau, ob nach 127 oder 315 oder 1587
' leeren Zellen vielleicht doch wieder eine gefülltge kommt.
' Also anders herum: Wir fangen unten an:
' Rows.Count gibt die maximale Anzahl der Zeilen an.
' Wenn man nur mit einer einzigen Excel Version arteitet,
' könnte man sie fest angeben. Da sie aber z.B. bei Excel2003
' Excel2010 unterschiedlich ist, machen wir da flexibel mit
' Rows.Count
' Cells (Rows.Count, 1) ist also die letzte Zelle in Spalte A.
' End(xlUp) entspricht den Tasten Strg-Pfeil nach oben.
' Damit kommen wir in die letzte gefüllte Zelle.
' Uns interessiert aber nicht die Zelle selbst oder deren Inhalt,
' sondern deren Zeilennummer. Deswegen .Row
' In z1 steht also dann die Nummer der letzten gefüllten
' Zelle in Spalte A:
z1 = Cells(Rows.Count, 1).End(xlUp).Row
' Was jetzt kommt, soll so lange durchlaufen werden,
' wie z1 größer ist als 1 (wir arbeiten uns bis zur ersten
' Zeile hoch):
While z1 \> 1
' Jetzt schauen wir uns die Zelle in Zeile z1, Spalte A an.
' Solagen diese Zelle nicht leer ist (Not IsEmpty) ...
While Not IsEmpty(Cells(z1, 1))
' ... erniedrigen wir die Zeilennummer um 1:
z1 = z1 - 1
' Wend ist die Abkürzung für "While End".
' Also wieder zurück zum Schleifenanfang:
Wend
' Wenn wir aus der Schleife rauskommen, ist z1
' die Nummer der ersten leeren Zelle
' über gefüllten Zellen
' z2 setzten wir noch eine Zeile darüber:
z2 = z1 - 1
' Jetzt wird z2 so lange erniedrigt, wie die Zellen leer sind.
' Und hier kommt jetzt der "Fehler", den wir abfangen wollen:
' Wenn ab hier nach oben hin alle Zellen leer sind,
' ist z2 irgendwann null. Cells(0, 1) ist nicht definiert.
' Es tritt ein Fehler auf, und wir springen zum Ende.
' Mit diesem Trick können wir uns eine Abfrage If z2 = 0 sparen.
While IsEmpty(Cells(z2, 1))
z2 = z2 - 1
Wend
' Die Differenz z1-z2 ist jetzt die Anzahl leerer Zellen zwischen gefüllten.
' Sind es weiger als 12 Zeilen, soll bis 12 Zeilen aufgefüllt werden.
' Wir müssen jetzt berechnen, wo und wieviele Zeile wir einfügen müssen:
' Wo: Über der unteren gefüllte Zeile (z1 war die erste leer Zelle darüber,
' also z1+1.
' Wieviele: z1-z2 leere Zeilen sind vorhanden, wir wollen 12 also:
' 12-(z1-z2).
' Das Rows Objekt erwartet aber nicht die Angaben VON und WIEVIELE,
' sondern VON und BIS. Also machen wir aus dem WIEVIELE, 12-(z1-z2), ein
' BIS, indem wir z1 noch mal dazuzählen: z1+(12-(z1-z2)).
' Soviele Zeile werden also eingefügt (Insert) und der Rest wird nach unten
' geschoben (Shift:=xlDown).
If z1 - z2
Wenn du den Makro im VBA Editor Schritt für Schritt laufen lässt (mit F8), kannst du genau nachverfolgen, wann welche Variable welchen Wert hat. Dazu musst du nur mit der Maus über einen Variablennamen fahren.
Und wenn du nach dem Insert-Schritt immer mal in die Tabelle umschaltest, siehst du wo und wieviele Zeilen eingefügt wurden.
ich hoffe mal, es hilft.
Gruß, Andreas
Hallo Andreas,
vielen vielen Dank für die Mühe.
Ich habe wieder sehr viel lernen können. Werde mir alles in Word kopieren und durcharbeiten. Das mit den Einzelschritten hatte ich heute morgen schon einmal ausprobiert.
Vielen Dank nochmal für die Geduld.
LG Martina
Hallo Andreas,
kannst Du mir vielleicht jetzt auch noch sagen, wie ich immer wieder die zwölf Monte in die Leerzeilen bekomme? Die muss ich noch einfügen.
Vielen Dank und lg
Martina
Hallo Andreas,
Hallo Martina,
kannst Du mir vielleicht jetzt auch noch sagen, wie ich immer
wieder die zwölf Monte in die Leerzeilen bekomme? Die muss ich
noch einfügen.
Was sind denn Monte? Ich denke, du meinst Monate?
Und in welcher Form sollen die da drin stehen?
Januar
Februar
…
oder
1.
2.
…
Und in welcher Spalte sollen sie stehen?
Vielen Dank und lg
Martina
Gruß, Andreas
Hallo Andreas,
sorry, ja natürlich meine ich Monate.
Sie müssten in dem Format 01.01.2013, 01.02.2013 - 01.012.2013 in Spalte N, beginnend in „N5“ stehen und dann wieder von vorne.Bzw ab nächstem Jahr dann 01.01.2014, 01.02.2014 ect. Ich habe das Problem schon einmal mit Autofill gelöst, aber leider habe ich jetzt festgestellt, dass meine Liste auch manchmal mehr als 12 Leerzeilen enthält und dann verschiebt sich das Ganze leider. Gibt es auch noch eine Möglichkeit, wenn mehr als zwölf Leerzeilen vorhanden sind, die überschüssigen zu löschen? Du hattest ja schrieben, dass die mehr an Leerzeilen nicht gelöscht werden, vielleicht ist das aber doch noch möglich.
Vielen Dank im voraus schon einmal für Deine Mühen, es hilft mir wirklich sehr.
LG Martina
Hallo Martina,
ich mach mich heute Abend dran.
Gruß, Andreas
Neue Version
Hallo Martina,
die neue Version des Makros passt jetzt die Zwischenräume an, d.h. er fügt Zeilen ein, wenn es weniger als 12 sind und löscht welche, wenn es mehr als 12 sind.
In Spalte N werden die Datums (heißt das so?) eingetragen, im gewüschten Format und immer mit der aktuellen Jahreszahl
' Jede Variable muss deklariert werden:
Option Explicit
Sub zeilenEinfügen()
' Wir brauchen 2 Zeiger auf Zeilennummern:
Dim z1 As Long, z2 As Long
' Wir brauchen eine Laufvariable in einer Schleife, die Zeilennummern darstellt:
Dim z As Long
' Hier definieren wir noch eine Textvariable, die wir mit der aktuellen Jahreszahl belegen:
Dim jahr As String
jahr = Format(Date, "yyyy")
' Damit da ganze etwas schneller geht, schalten wir die
' Bildschirmaktualisierung während der Zeilen-Einfügerei
' und -löscherei aus:
Application.ScreenUpdating = False
' Wenn ein "Fehler" auftritt (kann weiter unten passieren),
' soll zur Marke "schluss" gesprungen werden:
On Error GoTo schluss
' Wenn man das Ganze von oben nach unten durchlaufen würde,
' wüsste man nie genau, ob nach 127 oder 315 oder 1587
' leeren Zellen vielleicht doch wieder eine gefülltge kommt.
' Also anders herum: Wir fangen unten an:
' Rows.Count gibt die maximale Anzahl der Zeilen an.
' Wenn man nur mit einer einzigen Excel Version arteitet,
' könnte man sie fest angeben. Da sie aber z.B. bei Excel2003
' Excel2010 unterschiedlich ist, machen wir da flexibel mit
' Rows.Count
' Cells (Rows.Count, 1) ist also die letzte Zelle in Spalte A.
' End(xlUp) entspricht den Tasten Strg-Pfeil nach oben.
' Damit kommen wir in die letzte gefüllte Zelle.
' Uns interessiert aber nicht die Zelle selbst oder deren Inhalt,
' sondern deren Zeilennummer. Deswegen .Row
' In z1 steht also dann die Nummer der letzten gefüllten
' Zelle in Spalte A:
z1 = Cells(Rows.Count, 1).End(xlUp).Row
' Was jetzt kommt, soll so lange durchlaufen werden,
' wie z1 größer ist als 1 (wir arbeiten uns bis zur ersten
' Zeile hoch):
While z1 \> 1
' Jetzt schauen wir uns die Zelle in Zeile z1, Spalte A an.
' Solagen diese Zelle nicht leer ist (Not IsEmpty) ...
While Not IsEmpty(Cells(z1, 1))
' ... erniedrigen wir die Zeilennummer um 1:
z1 = z1 - 1
' Wend ist die Abkürzung für "While End".
' Also wieder zurück zum Schleifenanfang:
Wend
' Wenn wir aus der Schleife rauskommen, ist z1
' die Nummer der ersten leeren Zelle
' über gefüllten Zellen
' z2 setzten wir noch eine Zeile darüber:
z2 = z1 - 1
' Jetzt wird z2 so lange erniedrigt, wie die Zellen leer sind.
' Und hier kommt jetzt der "Fehler", den wir abfangen wollen:
' Wenn ab hier nach oben hin alle Zellen leer sind,
' ist z2 irgendwann null. Cells(0, 1) ist nicht definiert.
' Es tritt ein Fehler auf, und wir springen zum Ende.
' Mit diesem Trick können wir uns eine Abfrage If z2 = 0 sparen.
While IsEmpty(Cells(z2, 1))
z2 = z2 - 1
Wend
' Die Differenz z1-z2 ist jetzt die Anzahl leerer Zellen zwischen gefüllten.
' Sind es weiger als 12 Zeilen, soll bis 12 Zeilen aufgefüllt werden.
' Wir müssen jetzt berechnen, wo und wieviele Zeile wir einfügen müssen:
' Wo: Über der unteren gefüllte Zeile (z1 war die erste leer Zelle darüber,
' also z1+1.
' Wieviele: z1-z2 leere Zeilen sind vorhanden, wir wollen 12 also:
' 12-(z1-z2).
' Das Rows Objekt erwartet aber nicht die Angaben VON und WIEVIELE,
' sondern VON und BIS. Also machen wir aus dem WIEVIELE, 12-(z1-z2), ein
' BIS, indem wir z1 noch mal dazuzählen: z1+(12-(z1-z2)).
' Soviele Zeile werden also eingefügt (Insert) und der Rest wird nach unten
' geschoben (Shift:=xlDown).
If z1 - z2 12 Then
' ... dann stellen wir ähnliche Berechnungen an wie für das Einfügen,
' und löschen entsprechen viele Zeilen:
Rows(z2 + 1 & ":" & z2 + ((z1 - z2) - 12)).Delete
End If
' z2 ist weiterhin die erste gefüllte Zelle über den leeren Zellen.
' Also sollen jetzt unter z2 die Monate eingetragen werden:
' Das machen wir in einer Schleife.
' z Durchläuft die leeren Zeilen unterhalb von z2:
For z = z2 + 1 To z2 + 12
' Jetzt werden die Monate in die leeren Zellen in Spalte N eingetragen:
Cells(z, 14) = "01." & Format(z - z2, "00.") & jahr
' und die nächste Zeile / der nächste Monat
Next z
' Nach dieser Aktion setzen wir unsere erste Zeile z1 dahin, wo
' bisher z2 war (die erste gefüllte Zeile über den leeren Zeilen) ...
z1 = z2
' ... und springen wider zum Anfang der äußeren Schleife.
' Das Spiel geht von vorne los:
Wend
' Wenn wir bei Zeile 1 angekommen sind (remember: der Fehler),
' sind wir am "schluss". Der Bildschirm wird aktualisiert:
schluss: Application.ScreenUpdating = True
End Sub
Ich hoffe es läft alles wie gewünscht.
Gruß, Andreas
Hallo Andreas,
vielen vielen Dank für die Mühe. Es funktioniert super und spart mir sehr viel Arbeit. Außerdem vielen Dank für die ausführlichen Erklärungen. Es klappt alles.
Viele Grüße und noch mal vielen Dank
Martina
ausführlichen Erklärungen. Es klappt alles.
Hallo Martina,
„es klappt alles“?
Was ist denn mit deiner Aussage:
„in Spalte N, beginnend in „N5“ stehen“
Ich sehe das nicht im Code realisiert.
Und ja, Andreas hat das sehr ausführlich und gut erklärt.
Gruß
Reinhard