Thema: Leerzeilen füllen läuft ins Unendliche

Hallo in Runde,

ich stehe vor dem Problem, dass mein Makro, was Leerzeilen mit dem Wert der darüberliegenden Zelle füllen soll, am Ende der Spalte bis ins Unendliche füllt.
Er erkennt nicht das eigentlich Ende der anderen Zellen. Das Tabellenblatt endet in meinem Beispiel bei Zeile 4046, wird aber mit dem letzten Datum bis Zeile > 100.000 gefüllt.
Wie kann ich dies verhindern bzw. das Problem lösen? :-)

Makro sieht so aus:

Sub Datum ()
' Datum Makro
'
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

Beispieldatei:
https://www.herber.de/bbs/user/168721.xlsx

Ich hoffe auf Hilfe

LG Fio

"Er erkennt nicht das eigentlich Ende der anderen Zellen" ??? Warum sollte er?

Das Makro mach nur das, was DU programmiert hast:
Nämlich: Alle LEEREN Zellen in B mit der Formel befüllen.
Das Problem mit dem Computer sitzt meistens vor dem Bildschirm.

Private Sub Datum()

    Dim rng, lz

    lz = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

    Set rng = Range("B1:B" & lz)

    Set rng = rng.SpecialCells(xlCellTypeBlanks)

    rng.FormulaR1C1 = "=R[-1]C"

End Sub

Hi
das .SpecialCells orientiert sich immer am Zellbereich, der aktiv verwendet wird.
welcher das ist, hängt von verschiedenen Faktoren ab. In der Regel ist das die letzte Zelle, die mit Inhalt gefüllt ist (Wert oder Formel) oder eine eigene, vom Spalten- oder Zeilenstandard abweichende Formatierung hat.
auch beim Einfügen und Löschen von Zellen kann es manchmal vorkommen, dass Excel auf dem maximal erreichten Wert "hängen bleibt", da hier das Motto gilt "lieber etwas zugroß, aber niemals zu klein"

wenn dein Tabellenblatt in Zeile 4046 endet, aber das .SpecialCells noch Zeilen berücksichtigt, die weit darunterliegen, solltest du mal schauen, woran das liegt und ggf. die überzähligen Zellen löschen.
Das spart zum einen Speicherplatz, weil dann weniger Zellen gespeichert werden, zum anderen kann es Rechenvorgänge beschleunigen, die einen SVerweis, Zählen- oder SummeWenns mit ganzen Spalten als Zellbereich enthalten, da diese Funktionen dann immer nur den aktiven Zellbereich verwenden.

ansonsten Lösung wie von Onur gezeigt.

kleiner tipp noch:
dein Code:

Columns("B:B").Select

Selection.SpecialCells(xlCellTypeBlanks).Select

Selection.FormulaR1C1 = "=R[-1]C"

lässt sich so zu einer zeile zusammenfassen:

Columns("B:B").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

es lohnt sich immer, den vom Recorder aufgezeichneten Code in dieser Weise zu überarbeiten.

Gruß Daniel

Moin,

ist es angebracht, eine bereit beantworte Frage noch was hinzufügen? Ach, egal.

Man kann eine Kombi von UsedRange, Spalte 2 ("B") und Specialcells verwenden:

Private Sub Datum()

    On Error Resume Next 'weil SpecialCells eventuell keine Leerzelle findet

    Intersect(Columns(2), ActiveSheet.UsedRange).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

End Sub

VG
Yal

Hi
das ist aber nicht hilfreich, denn das .SpecialCells ist intern schon an die Usedrange gekoppelt, dh .SpecialCells wertet automatisch nur die Usedrange aus.
Es bringt also nichts, nochmal per Code die Usedrange mit SpecialCells zu kombinieren.

wenn man die letzte Zelle mit Inhalt in einer Spalte finden will, muss man diese mit Cells(Rows.count, 1).end(xlup) von unten "anspringen" oder mit .Find dann suchen.

Gruß Daniel

Hmm... in der Tat. Ich hatte noch keine Gedanken darüber gemacht aber gerade eben getestet.
Schon wieder was gelernt.

Wenn ich Zeile im unteren Bereich von UsedRange lösche, bleibt UsedRange an der bisherige Bereich. D.h. alle gelöschte Zeilen kommen in dem SpecialCells(xlCellTypeBlanks)!

Fällt mir gerade auch ein: wenn am Ende von der Spalte B einige Zellen leer sind, kommt Cells(Rows.Count, 2).End(xlUp) "zu hoch".

VG
Yal

natürlich.
für das Ermitteln der letzten Befüllten Zeile darf man auch nicht die Spalte nehmen, in welcher man die Daten ergänzen will, sondern man muss die Spalte verwenden, die die relevante Datenmenge vorgibt, hier also Spalte A.
ein bisschen Mitdenken muss schon sein.
Gruß Daniel

Vielen vielen Dank.

Es klappt und ich bin so happy :-))

LG Fio

Vielleicht habe ich mich nicht gut genug ausgedrückt.
Dass er genau das macht, was ich programmiert habe, sehe ich ja. Hätte es halt gern anders.

Meine Frage sollte dann besser lauten:

Kann man vorher die Anzahl Zellen einer Spalte ermitteln und diesen Wert in das Makro zum Leerzellen auffüllen einbauen, damit es eben nicht in Unendliche füllt.


LG

P.S. Ich hatte angegeben, dass ich keine VBA Erfahrung habe und ich die Scripte per Makro Aufzeichnung erstelle. Daher hoffte ich auf Hilfe.