Microsoft Excel

Herbers Excel/VBA-Archiv

Range mit Variable für End(xlUp)

Betrifft: Range mit Variable für End(xlUp) von: Christian
Geschrieben am: 25.09.2014 16:12:06

Hallo zusammen,

wer kann mir helfen?

Kurz: Ich möchte die letzte Zeile in einem Tabellenbereich finden, um die darüberliegende Zeile an diese Stelle zu kopieren. Unter diesem Tabellenbereich sind jedoch weitere Zellen belegt.

Prinzipiell würde ich dafür mit
A = Range ("A14").End(xlUp).Row - 1 arbeiten. Leider ist der Range aber dann aufgebraucht, wenn ich die Zeile das erste mal kopiert habe.

Es müsste also mit Variablen gelöst werden:
A = Cells(2,i+1).End(xlUp).Row -1

Aber wie kann ich das brauchbar umsetzen?

Wenn ich also mit einer Schleife arbeite,

Besten Dank!

  

Betrifft: AW: Range mit Variable für End(xlUp) von: Gerold
Geschrieben am: 25.09.2014 18:39:05

Hallo Christian

Zeilennummer innerhalb der Schleife ermitteln.

'zum Beispiel 5 mal
for  b = 1 to 5
'Letzte zeilenummer ermitteln
   A = Range ("A14").End(xlUp).Row - 1

' code zum Kopieren

next

Gruß Gerold

Rückmeldung wäre nett.


  

Betrifft: AW: Range mit Variable für End(xlUp) von: Christian
Geschrieben am: 26.09.2014 11:18:34

Danke Gerold,

mein Problem ist nur leider noch nicht ganz klar geworden.

Ich erläutere kurz die Funktion:

Ausführung des Makros: letzte Zeile im Bereich suchen, vorletzte Zeile an diese Stelle kopieren
Erneutes Ausführung des Makros: letzte Zeile im Bereich suchen (Achtung: Diese muss nun einen veränderten Range haben), vorletzte Zeile an diese Stelle kopieren.

Dadurch, dass die Range, in der die letzte Zeile gesucht werden soll, auch immer größer wird, muss ich das in meine Variable einbauen, und dafür suche ich eine Funktion.

Danke!


  

Betrifft: AW: Range mit Variable für End(xlUp) von: Gerold
Geschrieben am: 26.09.2014 16:15:40

Hallo Christian

Etwa so?


Sub TabellenErweitern()
Dim a As Long, b As Long
'zb 5 mal Kopieren
For b = 1 To 5
'Letzte zeilenummer ermitteln
   a = ActiveSheet.Range("A14").End(xlUp).Row
   
'Zeile Einfügen/Anhängen - wenn nicht gewünscht die Zeile löschen
    ActiveSheet.Cells(a + 1, 1).EntireRow.Insert
    
'Zeile Kopieren
    ActiveSheet.Cells(a - 1, 1).EntireRow.Copy

'Kopiertes Einfügen
    ActiveSheet.Cells(a + 1, 1).Select
    ActiveSheet.Paste
Next
End Sub


Gruß Gerold

Rückmeldung wäre nett.


  

Betrifft: AW: Range mit Variable für End(xlUp) von: Christian
Geschrieben am: 29.09.2014 14:43:24

Hallo Gerold,

dein Code ist sehr verständlich, danke dafür.

Jedoch muss ich eine Anmerkung hineinsetzen, um mein Problem nochmals zu erläutern.

Ich möchte, dass die Range variabel ist.


Sub TabellenErweitern()
Dim a As Long, b As Long
'zb 5 mal Kopieren
For b = 1 To 5
'Letzte zeilenummer ermitteln => die letzte Zeile verändert sich mit jedem Schleifendurchlauf
'for b=1 =>    a = ActiveSheet.Range("A14").End(xlUp).Row
'for b=2 =>    a = ActiveSheet.Range("A15").End(xlUp).Row
'for b=3 =>    a = ActiveSheet.Range("A16").End(xlUp).Row
.
.
.
'for b=x =>    a = ActiveSheet.Range("Ax").End(xlUp).Row

   a = ActiveSheet.Range("A14").End(xlUp).Row
   
'Zeile Einfügen/Anhängen - wenn nicht gewünscht die Zeile löschen
    ActiveSheet.Cells(a + 1, 1).EntireRow.Insert
    
'Zeile Kopieren
    ActiveSheet.Cells(a - 1, 1).EntireRow.Copy

'Kopiertes Einfügen
    ActiveSheet.Cells(a + 1, 1).Select
    ActiveSheet.Paste
Next
End Sub
Meine Idee wäre deshalb anstelle der Range mit Cells direkt zu arbeiten
'for b=1 =>    a = ActiveSheet.Cells(1,14+b)End(xlUp).Row
Jedoch wird bei jedem Start des Makros die Schleife neu gestartet, somit fängt die Varible b immer bei 1 an. Sie soll aber bei der letzten gemerkten freien Zelle anfagen...

Hast du noch eine Idee oder ist mein Problem deutlicher geworden?


  

Betrifft: AW: Range mit Variable für End(xlUp) von: Gerold
Geschrieben am: 29.09.2014 18:22:08

Hallo Christian

durch

a = ActiveSheet.Range("A14").End(xlUp).Row

wird die letzte Zeile doch jedesmal wieder neu berechnet



Mit deinem Voerschlag

a = ActiveSheet.Cells(1,14+b).End(xlUp).Row

erhälst du immer für a eine 1 weil
ActiveSheet.Cells(zeile(bei dir die 1),Spalte(bei dir spalte 14+b)).End(xlUp).Row

Stell doch mal eine Beispieltabelle rein damit man sich mal ein Bild machen kann
was, wann und wo kopiert werden soll.

Gruß Gerold

Rückmeldung wäre nett.


  

Betrifft: AW: Range mit Variable für End(xlUp) von: Gerold
Geschrieben am: 29.09.2014 23:06:44

Hallo Christian

Hab da mal ein Beispiel hochgeladen

https://www.herber.de/bbs/user/92890.xlsm

Teste mal Seite "Beispiel 2"

Mfg Gerold

Rückmeldung wäre nett.


  

Betrifft: AW: Range mit Variable für End(xlUp) von: Christian
Geschrieben am: 30.09.2014 10:08:12

Hallo Gerold,

danke!

So funktioniert es. Die Schleife kann ich an dieser Stelle sogar weglassen.

Mein Denkfehler war, dass ich bei End(xlUp) immer davon ausgegangen bin, dass er im angegebenen Range von unten nach oben sucht. Tatsächlich sucht er aber von oben nach unten, und klar gibt es immer eine Leerzeile, wenn ich eine weitere Zeile einfüge...

So funktioniert mein Befehl.

Als nächstes werde ich es noch optimieren, dass der Zelleninhalt gelöscht wird (benötige nämlich nur die Formeln) und dass das Makro automatisch ausgeführ wird, wenn in der vorletzt-beschriebenenen Zeile in Zelle x eine Eingabe mit Enter bestätigt wird.


  

Betrifft: AW: Range mit Variable für End(xlUp) von: Gerold
Geschrieben am: 30.09.2014 11:09:49

Hallo Christian


"dass er im angegebenen Range von unten nach oben sucht"

Die Suchrichtung kann man selbst bestimmen


z.B.
- Range("A1").End(xlDown).Row --> suche nach unten
- Range("A100").End(xlUp).Row --> suche nach oben"
- Range("A100").End(xlToLeft).Row --> suche nach links"
- Range("A1").End(xlToRight ).Row --> suche nach rechts"



Gruß Gerold


 

Beiträge aus den Excel-Beispielen zum Thema "Range mit Variable für End(xlUp)"