Letzte Zeilennr mit Inhalt mit VBA

Bild

Betrifft: Letzte Zeilennr mit Inhalt mit VBA
von: Jean Gitanes
Geschrieben am: 06.08.2015 15:23:13

Hallo,
mich interessiert wie man in die Variable LZN die letzte (höchste) Zeilennummer der Zelle mit Inhalt in Spalte A ermitteln und speichern kann.
Gruß
Jean

Bild

Betrifft: AW: Letzte Zeilennr mit Inhalt mit VBA
von: Daniel
Geschrieben am: 06.08.2015 15:33:12
Hi
kommt darauf an, wie du "mit Inhalt" definierst.
hat eine Zelle mit einer Formel mit einem Formelergebnis Leerstring "" für dich einen Inhalt oder nicht?
probier mal:
LZN = Columns(1).Find(what:="?*", lookat:=xlwhole, lookin:=xlvalues, Searchdirection:=xlprevious).row
über den Parameter LookIn kannst du steuern, ob Zellen mit dem Formelergebnis "" als
- Leerzellen: LookIn:=xlvalues
- Mit Inhalt: LookIn:=xlformulas
gewertet werden.
Gruß Daniel

Bild

Betrifft: Danke und eine Zusatzfrage
von: Jean Gitanes
Geschrieben am: 06.08.2015 15:44:19
Hallo Daniel,
Deine Befehlszeile macht genau das was sie soll. Danke für den Tipp.
Wenn man jede zweite Zeile löschen soll, beginnt man dann sinnvollerweise von der letzten Zelle aus gesehen oder von oben?
Gruß
Jean

Bild

Betrifft: Zeilen löschen: Von unten. owT
von: Rudi Maintaire
Geschrieben am: 06.08.2015 16:00:10


Bild

Betrifft: Danke ...
von: Jean Gitanes
Geschrieben am: 06.08.2015 16:03:03
Danke, Rudi.
Jetzt muss ich es nur noch hinbekommen ...
Gruß
Jean

Bild

Betrifft: AW: Danke und eine Zusatzfrage
von: Daniel
Geschrieben am: 06.08.2015 16:02:00
Hi
beim Löschen von Zeilen rutschen die nachfolgenden Zeilen nach oben nach.
löscht du jetzt von oben nach unten, passiert das in dem Zellbereich den du noch bearbeiten musst und du müsstest diese Veränderung in deiner Programmierung berücksichtigen.
löscht du jedoch von unten nach oben, passiert das Nachrutschen in dem Zellbereich den du schon bearbeitet hast und es ist für dich nicht relevant.
allerdings ist das löschen von Zeilen per Schleife sehr langsam.
besser ist, in einer Hilfsspalte alle Zeilen, die gelöscht werden sollen per Formel mit 0 zu kennzeichnen und die die stehen bleiben sollten mit der Zeilennummer.
dann schreibt man noch in die ersten Zeile (überschrift) die 0 und wendet dann das DUPLIKATE ENTFERNEN auf die Tabelle mit der Hilfsspalte als kriteriumsspalte an.
Bei entsprechend grossen Datenmengen bin dich damit von Hand wahrscheinlich schneller als ein Makro mit Schleife
in deinem fall wäre die Formel:

=Wenn(Rest(Zeile();2)=0;0;Zeile())

oder
=Wenn(Rest(Zeile();2)=0;Zeile();0)

je nach dem ob du gerade oder ungrade Zeilen löschen willst.
geht natürlich auch als Code:
with ActiveSheet.Usedrange
    With .columns(.columns.count + 1) ' erste freie Spalte am Tabellenende
         .formulaR1C1 = "=IF(Mod(Row(),2)=0,0,Row())"
         .Cells(1, 1).Value = 0
         .EntireRow.Removeduplicates .column, xlno
         .clearContents
    end with
end with
gruß Daniel

Bild

Betrifft: Danke sehr ... mit Zusatzfrage
von: Jean Gitanes
Geschrieben am: 06.08.2015 16:12:22
Hallo Daniel,
vielen Dank für Deine Hilfen. Dein Makro hilft mir sehr. Danke.
Und wie geht man vor, wenn man Zeilen einfügen müsste?
Gruß
Jean

Bild

Betrifft: AW: Danke sehr ... mit Zusatzfrage
von: Daniel
Geschrieben am: 06.08.2015 16:54:28
Hi
per Schleife auch von unten, aus dem gleichen Grund.
die Veränderungen passieren im schon bearbeiteten Bereich und müssen daher in der Programmierung nicht berücksichtigt werden.
Schnelles einfügen von vielen Zeilen bei grossen Datenmengen ist etwas komplizierter.
hierfür habe ich noch keinen "Standardcode" der mit leichter abwandlung auf alle Fälle passt.
in der regel nutze ich aber das Sortieren aus, um zusätzliche Zeilen in eine Tabelle einzufügen, weil Excel beim Sortieren sehr schnell ist.
um beispielsweise in einer Tabelle nach jeder Zeile eine Leerzeile einzufügen würde ich so vorgehen:
1. in einer Hilfsspalte die Zeilennummern rausschreiben (=Zeile())
2. diese Zeilennummern kopieren und unten an die Hilfsspalte nochmal als Wert einfügen
3. die ganze Tabelle nach der Hilfsspalte sortieren
4. Hilfsspalte wieder löschen
das Prinzp ist eigentlich immer ganz einfach:
1. wie löse ich die Aufgabe ohne VBA mit Excelmethoden am schnellsten und einfachsten
2. per VBA erstelle ich dann einen Code, der genau diese Schritte ausführt.
das gibt dann in der Regel kurzen, schnellen und einfachen Code, bei dem man sich beim Erstellen zusätzlich vom Recorder helfen lassen kann.
Gruß Daniel

Bild

Betrifft: Klasse ... Mit einer Bitte
von: Jean Gitanes
Geschrieben am: 06.08.2015 17:20:22
Hallo Daniel,
das ist einfach Klasse, wie Du den Lösungsansatz erklärst. Vielen, vielen Dank.
Ich bin Anfänger und lese Deine Zeilen mit hohem Wissensgewinn.
Wie sähe denn ein Code aus, wenn zB bei 50 lückenlosen Zeilen jeweils eine Leerzeile einzufügen wäre und die bisherige Zeile 1 unverändert bleiben soll, d.h. die Zeile 1 bleiben soll?
Gruß
Jean

Bild

Betrifft: AW: Klasse ... Mit einer Bitte
von: Daniel
Geschrieben am: 06.08.2015 17:36:49
Hi
naja, Zeile 1 bleibt unveränder, sowieso.
die Frage ist, soll schon nach Zeile 1 eine Leerzeile eingefügt werden oder erst nach Zeile 2.
ausserdem würde ich es gut finden, wenn du erstmal selber aktiv wirst, dh das ganze so wie ich es beschrieben habe ausprobierst und dann auch versuchst, es mit Hilfe es Recorders oder wie weit halt deine Fähigkeiten sind, selber den Code dazu zu erstellen.
wenn du dann was hast, können wir zusamemn noch mal drüber schauen, wie du daraus funktionsfähigen Code machst.
Gruß Daniel

Bild

Betrifft: AW: Ein erster Versuch ...
von: Jean Gitanes
Geschrieben am: 06.08.2015 20:14:13
Hallo Daniel,
der erste Versuch klappt bis zum Sortieren, Grund unbekannt ...
Es wäre schön, wenn Du mal den Ansatz durchsehen könntest und Verbesserungen anbringen würdest, damit das Ding auch läuft.

Sub test()
hsp = 4  'HilfsSpaltenNr
azn = 2  'AnfangsZeilenNr
'lzn Letzte ZeilenNr
With ActiveSheet.UsedRange
lzn = Columns(1).Find(what:="?*", lookat:=xlWhole, LookIn:=xlValues, Searchdirection:= _
xlPrevious).Row
End With
anzahl = lzn - azn + 1 'anzahl = echte Zeilen
For i = azn To lzn
    Cells(i, hsp) = Cells(i, hsp).Row()
    Cells(i + anzahl, hsp).Value = Cells(i, hsp).Value
Next i
Range(Cells(azn, hsp), Cells(azn + 2 * anzahl - 1)).Select
' das Sortieren klappt nicht
End Sub
Gruß
Jean

Bild

Betrifft: AW: Ein erster Versuch ...
von: Daniel
Geschrieben am: 06.08.2015 21:20:43
Hi
sortieren dann so, das ist allerdings noch die alte schreibweise, wie sie bis 2003 verwendet wurde.
die neue, welcher der Recorder aufzeichnet ist mir zu aufwendig.
Ein Fehler bei dir ist die Range, du hast beim zweiten Cells die Spaltennummer vergessen:
so gehts am schluss:

Range(Cells(azn, 1), Cells(azn + 2 * anzahl - 1, hsp)).Sort _
                                                      key1:=Cells(azn, hsp), _
                                                      order1:=xlAscending, _
                                                      Header:=xlNo
Columns(hsp).ClearContents
Prinzipell gefällt mir das schon mal ganz gut, nur das mit der Schleife ist suboptimal.
du solltest versuchen, Zellen nie einzeln zu bearbeiten, sondern immer als Block, beispielsweise durch das verwenden von Formeln. Hier würde sich die Funktion ZEILE() anbieten, um die Zeilennummer zu ermitteln:
Sub test()
hsp = 4  'HilfsSpaltenNr
azn = 2  'AnfangsZeilenNr
'lzn Letzte ZeilenNr
With ActiveSheet.UsedRange
lzn = Columns(1).Find(what:="?*", lookat:=xlWhole, LookIn:=xlValues, Searchdirection:= _
xlPrevious).Row
End With
anzahl = lzn - azn + 1 'anzahl = echte Zeilen
With Range(Cells(azn, hsp), Cells(lzn, hsp))
    .FormulaLocal = "=Zeile()"
    .Copy
    With .Resize(.Rows.Count * 2)
        .PasteSpecial xlPasteValues
        .EntireRow.Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
        .ClearContents
    End With
End With
End Sub
Gruß Daniel

Bild

Betrifft: DIe Lösung - vielen Dank
von: Jean Gitanes
Geschrieben am: 06.08.2015 21:48:57
Hallo Daniel,
vielen Dank für Deine Lösung.
Da sieht man schon den Unterschied zwischen einem Profi und mir Anfänger.
Ich habe viel gelernt, bin sicher, dass noch viel zu tun ist.
Es macht Spaß und so werde ich mich an anderen konkreten Aufgaben weiter vertiefen in VBA
Für Deine zahlreichen Hilfen, Anregungen besten Dank.
Einen schönen Abend.
Gruß
Jean

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Letzte Zeilennr mit Inhalt mit VBA"