Dynamischen Bereich per VBA ansprechen

Forumthread: Dynamischen Bereich per VBA ansprechen

11.01.2021 19:15:21

Tom

Ein hallo an die Excel-VBA-Experten,
ich habe (mit dem Makrorecorder) ein Makrocode gebastelt, welcher mir das markieren einer Spalte ab Anfangszelle bis zur Endzelle des Bereiches und anschließend das kopieren und einfügen der (durch Zellformeln berechneten Ergebnisse) als Werte an anderer Stelle der Tabelle einfügt.
Auszug aus dem Code:
Option Explicit

Range("DI5:DI354").Select
Application.CutCopyMode = False
Selection.Copy
Range("DG5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Insgesamt wird dieser Teil-Code an 12 verschiedenen Bereichen wiederholt.
Da sich der zu bearbeitende Bereich (Anzahl der Zeilen = Datensätze) ändern kann, muß ich bisher das Ende des zu kopierenden Bereiches :DI354 vor der Ausführung des Makros prüfen und ggf. händisch präzisieren.
Ich suche nach einer Möglichkeit die Anpassung des Bereiches variabel durch das Makro auf die jeweils entsprechend letzte Zeile erledigen zu lassen.
Dazu habe ich mir in der Berechnungstabelle, in der Zelle $B$3 mit der Formel =ANZAHL2(A:A) die letzte Zeile der Tabelle ausgeben lassen.
Leider gelingt es mir nicht, die 354 durch den (variablen) Wert der Zelle $B$3 zu ersetzen.
Ein einfaches markieren der gesamten Spalte funktioniert nicht, weil in den Kopfzeilen der Tabelle Spaltenbeschriftungen und Berechnungswerte stehen, die z.T. durch absolute Bezüge in den Zellen weiter unten verwendet werden und unterhalb der Tabelle diverse Summen / Auswertungen folgen.
Es wäre schön, wenn mir ein wissender Mensch helfen könnte.
Ich bin im Forum leider nicht fündig geworden
L.G.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Dynamischen Bereich per VBA ansprechen

11.01.2021 19:42:46

Daniel

Range("DI5:DI" & Range("B3").Value).Select

Btw, in VBA ist es nicht notwendig, den Zellbereich erst zu selektieren und mit Selection die Aktion auszuführen, du kannst auch die Aktion (das .Copy) direkt an das Range anhängen (anstelle des Selects).
Für das .PasteSpecial gilt das gleiche.
Der Recorder muss so aufzeichnen, weil die Maus uns dazu zwingt so zu arbeiten, aber man sollte den Code diesbezüglich immer überarbeiten, weil er dadurch kürzer, schneller und weniger fehleranfällig wird.
Du kannst auch die Zelle B3 einsparen und die Berechnung in VBA ausführen:

Range("DI5:DI" & Worksheetfunction.CountA(Columns(1))).Copy

Gruß Daniel

AW: Dynamischen Bereich per VBA ansprechen

11.01.2021 20:05:24

Oisse

Meintest Du so?

Option Explicit
Sub SpaltenKopieren()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim i, lz, Startwert As Long
Dim Spalte, Spalte2 As String
Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
Startwert = 5
Spalte = InputBox("Welche Spalte möchtest Du kopieren?", "Spalte wählen")
Spalte2 = InputBox("In welche Spalte möchtest Du einfügen?", "Spalte wählen")
With ws
lz = .Range(Spalte & "65636").End(xlUp).Row
.Range(Spalte & Startwert & ":" & Spalte & lz).Copy
.Paste Destination:=.Range(Spalte2 & Startwert)
End With
End Sub

Gruß Oisse

AW: Dynamischen Bereich per VBA ansprechen

12.01.2021 07:42:35

Tom

Hallo Daniel,
hallo Oisse,
danke für die schnelle Reaktion.
Ich melde mich wieder!

AW: Dynamischen Bereich per VBA ansprechen

12.01.2021 21:27:57

Tom

Hallo,
nach dem Praxistest der vorgeschlagenen Lösungen, hab ich mich für die Lösung von Daniel (erste Code-Zeile) entschieden, weil ich diese für mich leicher in den bestehenden VBA-Code integrieren kann.
Herzlichen Dank für eure Unterstützung und Hilfe.
Tom
Denkt weiter positiv, aber bleibt bitte nach Möglichkeit negativ!

Infobox / Tutorial

Dynamischen Bereich in Excel per VBA ansprechen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um einen dynamischen Bereich in Excel per VBA anzusprechen, kannst du folgende Schritte befolgen:

Öffne den Visual Basic for Applications (VBA) Editor mit ALT + F11.
Füge ein neues Modul hinzu, indem du im Projektfenster mit der rechten Maustaste auf „VBAProject (DeineDatei.xlsx)“ klickst und dann „Einfügen“ -> „Modul“ wählst.

Schreibe den folgenden Code, um den Bereich dynamisch zu definieren und die Werte zu kopieren:

Option Explicit
Sub SpaltenKopieren()
   Dim wb As Workbook
   Dim ws As Worksheet
   Dim rng As Range
   Dim lz As Long
   Set wb = ThisWorkbook
   Set ws = wb.Worksheets(1)

   ' Bestimme die letzte Zeile in Spalte A
   lz = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   ' Kopiere den dynamischen Bereich
   ws.Range("DI5:DI" & lz).Copy
   ws.Range("DG5").PasteSpecial Paste:=xlPasteValues
End Sub

Schließe den VBA-Editor und führe das Makro aus, um den dynamischen Bereich zu kopieren.

Häufige Fehler und Lösungen

Fehler: Das Makro läuft nicht, weil die letzte Zeile nicht richtig erkannt wird.
- Lösung: Stelle sicher, dass du die richtige Spalte für die Bestimmung der letzten Zeile verwendest. In obigem Beispiel wird die letzte Zeile in Spalte A ermittelt.
Fehler: Der Bereich wird nicht korrekt kopiert.
- Lösung: Überprüfe, ob der Bereich, den du kopieren möchtest, tatsächlich Daten enthält und nicht leer ist.

Alternative Methoden

Falls du das Kopieren ohne VBA durchführen möchtest, kannst du den Inhalt einer Zelle dynamisch ansprechen, indem du die Funktion =ANZAHL2(A:A) in eine Zelle einfügst. Diese wird dann zur Bestimmung der letzten Zeile genutzt.

Ein weiterer Ansatz ist, die INDEX-Funktion in Kombination mit VERGLEICH zu verwenden, um den letzten Eintrag in einer Liste zu finden.

Praktische Beispiele

Ein einfaches Beispiel, um Excel-Kopfzeilen und Daten zu kopieren:

Sub KopfzeileUndDatenKopieren()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    ' Kopfzeile kopieren
    ws.Rows(1).Copy Destination:=ws.Rows(5)

    ' Daten kopieren
    Dim letzteZeile As Long
    letzteZeile = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A2:A" & letzteZeile).Copy Destination:=ws.Range("B2")
End Sub

Tipps für Profis

Nutze With-Anweisungen, um deinen Code übersichtlicher und schneller zu gestalten.
Vermeide die Verwendung von .Select oder Selection, um die Ausführungsgeschwindigkeit deines Makros zu verbessern.
Teste deinen Code Schritt für Schritt in der Entwicklungsumgebung, um Fehler schnell zu identifizieren.

FAQ: Häufige Fragen

1. Wie kann ich eine Excel-Zelle dynamisch ansprechen?
Du kannst eine Zelle dynamisch ansprechen, indem du die Range-Funktion mit Variablen verwendest, wie im obigen Beispiel gezeigt.

2. Ist es möglich, Kopfzeilen ohne Makro zu kopieren?
Ja, du kannst Excel-Kopfzeilen z.B. über die Funktion „Kopieren“ und „Einfügen“ manuell kopieren, ohne VBA zu verwenden.

Beliebteste Forumthreads (12 Monate)

Dynamischen Bereich per VBA ansprechen

Forumthread: Dynamischen Bereich per VBA ansprechen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Dynamischen Bereich in Excel per VBA ansprechen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread