271 Aufrufe
Gefragt in Tabellenkalkulation von peters Mitglied (460 Punkte)

Moin!

Ich ermittle per VBA die letzte genutzte Zelle eines Tabellenblattes wie folgt:

LetzteZelle = Range("A1").SpecialCells(xlCellTypeLastCell).Address

Das funtioniert auch und die korrekte Zellenadresse lässt sich über MsgBox() auch ausgeben.

Aber wie nutze ich den Wert nun in der Range()-Funktion in der Art wie

Range("A2:LetzteZelle").Select


Meine diversen Versuche produzierten ausschließlich Fehler.

Gruß

Peter

6 Antworten

0 Punkte
Beantwortet von gmg-cc Mitglied (338 Punkte)

Moin Peter,

davon einmal abgesehen, dass deine Variable LetzteZelle nicht in jedem Fall die letzte Daten-Zeile des Blattes wiedergibt hast du diese Variante gewiss noch nicht probiert:

Range("A2:" & LetzteZelle).Select

Günther

0 Punkte
Beantwortet von peters Mitglied (460 Punkte)
Hallo Günther,

doch, diese Variante hatte ich bereits erfolgos probiert. Ergibt ebenfalls eine Fehlermeldung.

Peter
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Peter,

welche Fehlermeldung kommt den?

Der Code müsste so funktionieren, wie Günther es geschrieben hat.

Gruß

M.O.
0 Punkte
Beantwortet von peters Mitglied (460 Punkte)

Hallo MO,

es kommt Laufzeitfehler 1004: Die Autofill-Methode des Range-Objektes konnte nicht ausgeführt werden.

Der Code sieht so aus:

Sub Formel_kopieren_test_letztezelle()
'
' Formel_kopieren Makro
   
   letztezelle = Range("A1").SpecialCells(xlCellTypeLastCell).Address
    
    Range("Y2:AD2").Select
    Selection.AutoFill Destination:=Range("Y2:" & letztezelle).Select, Type:=xlFillDefault
    Range("Y2:" & letztezelle).Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

Wenn ich Range konkret mit Range("Y2:AD34300") angebe, funzt es.

Gruß

Peter

+1 Punkt
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Peter,

den Autofill-Befehl kenne ich so nicht; da ist das Select am Ende zu viel.

Ich nehme mal an, du willst hier Formeln in den Spalten Y bis AD herunterkopieren. Dann ermittle nur die letzte Zeile und nutze diese für deinen Code:

Sub Formel_kopieren_letztezelle()
Dim LetzteZelle As Long
   
'letzte beschriebene Zeile festlegen
LetzteZelle = Range("A1").SpecialCells(xlCellTypeLastCell).Row

With Range("Y2:AD" & LetzteZelle)
   .FillDown                              'nach unten auffüllen
   .Copy                                  'kopieren
   .PasteSpecial Paste:=xlPasteValues     'Werte einfügen
End With
    
'Kopierbereich aufheben
Application.CutCopyMode = False
    
End Sub


Dann bist du auch sicher, dass der richtige Bereich bearbeitet wird. Wie du siehst kannst du das Auffüllen kurz halten. Auf Select kannst du in den meisten Fällen verzichten.

Gruß

M.O.

0 Punkte
Beantwortet von peters Mitglied (460 Punkte)

Hallo MO,

danke für den Lösungsvorschlag.

Ich hatte nebenher ebenfalls weiter getüftelt und bin auf folgende Lösung gekommen:

Sub Formel_kopieren_2()
  
   Dim LetzteZelle As Long
   LetzteZelle = Range("A1").SpecialCells(xlCellTypeLastCell).Row
   
    Set SourceRange = Worksheets("Filmarchiv").Range("Y2:AD2")
    Set fillRange = Worksheets("Filmarchiv").Range("Y2:AD" & LetzteZelle)
    SourceRange.AutoFill Destination:=fillRange

    
    Range("Y3:AD" & LetzteZelle).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      
End Sub

Deine Lösung hätte ich auch umbauen müssen, weil diese mir die Formeln in Zeile 2 zerschiesst,
und da ich mit einer With-Schleife noch überhaupt nichts gemacht habe, bin ich bei dieser Lösung hier gelandet.

Gruß

Peter

...