Tutorial

Excel-Makros für dynamische Tabellen: Formatieren, Kopieren und Summieren (unabhängig von der Zeilenanzahl)

Melden

Hier ist ein umfassendes Tutorial, wie du Excel-Makros erstellst, die sich automatisch an die Größe deiner Tabelle anpassen.

Wer mit Excel arbeitet, kennt das Problem: Ein Makro, das heute perfekt funktioniert, versagt morgen, weil die neue Datentabelle plötzlich 100 Zeilen mehr hat. In diesem Tutorial lernst du, wie du dynamische Makros schreibst, die immer das Ende deiner Daten finden.

Teil 1: Die Vorbereitung

Bevor wir starten, stelle sicher, dass die Registerkarte Entwicklertools in deinem Excel aktiv ist.

  1. Rechtsklick auf ein beliebiges Tabellen-Register -> Menüband anpassen.
  2. Setze rechts den Haken bei Entwicklertools.
  3. Drücke ALT + F11, um den VBA-Editor zu öffnen.
  4. Gehe auf Einfügen -> Modul.

Teil 2: Das Kern-Konzept – Die letzte Zeile finden

Der wichtigste Befehl für dynamische Makros ist das Finden der letzten genutzten Zeile. Wir nutzen dafür den Befehl End(xlUp).

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Erklärung: Excel springt von der allerletzten Zelle in Spalte A (1) nach oben, bis es auf den ersten Inhalt stößt.

Teil 3: Das vollständige Makro

Kopiere diesen Code in dein Modul. Er ist so aufgebaut, dass er eine Tabelle formatiert, die Summe berechnet und die Daten auf ein anderes Blatt kopiert.

Sub DynamischeTabelleBearbeiten()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long

    Set ws = ActiveSheet

    ' 1. Die letzte Zeile und Spalte finden
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' 2. FORMATIEREN: Den gesamten Bereich rahmen und Header fett machen
    With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        .Borders.LineStyle = xlContinuous
        .Font.Name = "Calibri"
    End With

    ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Font.Bold = True
    ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Interior.Color = RGB(200, 200, 200)

    ' 3. SUMMIEREN: Summe unter der letzten Zeile in Spalte B (Beispiel)
    ' Wir nehmen an, in Spalte B stehen Werte
    ws.Cells(lastRow + 1, 2).Formula = "=SUM(B2:B" & lastRow & ")"
    ws.Cells(lastRow + 1, 2).Font.Bold = True
    ws.Cells(lastRow + 1, 1).Value = "GESAMT:"

    ' 4. KOPIEREN: Die gesamte Tabelle auf ein neues Blatt kopieren
    ' Wir erstellen ein neues Blatt oder nutzen ein vorhandenes namens "Archiv"
    On Error Resume Next
    Dim targetSheet As Worksheet
    Set targetSheet = Sheets("Archiv")
    If targetSheet Is Nothing Then
        Set targetSheet = Sheets.Add(After:=Sheets(Sheets.Count))
        targetSheet.Name = "Archiv"
    End If
    On Error GoTo 0

    ' Den Bereich (inkl. der neuen Summenzeile) kopieren
    ws.Range(ws.Cells(1, 1), ws.Cells(lastRow + 1, lastCol)).Copy Destination:=targetSheet.Range("A1")

    MsgBox "Formatierung, Summe und Kopieren abgeschlossen!", vbInformation
End Sub

Teil 4: Die wichtigsten Code-Abschnitte erklärt

A. Dynamische Bereiche ansprechen

Statt Range("A1:C10") zu schreiben, verwenden wir Range(Cells(1, 1), Cells(lastRow, lastCol)).

  • Cells(1, 1) ist oben links (A1).
  • Cells(lastRow, lastCol) ist die Zelle ganz rechts unten in deinem Datensatz. Egal wie groß die Tabelle wird, VBA findet immer den Rahmen.

B. Die Summen-Formel dynamisch einfügen

ws.Cells(lastRow + 1, 2).Formula = "=SUM(B2:B" & lastRow & ")"

Hier nutzen wir "String-Verkettung". Wenn lastRow 50 ist, schreibt Excel =SUM(B2:B50). Wenn sie 5000 ist, schreibt es =SUM(B2:B5000).

C. Das Kopieren

Mit Destination:=targetSheet.Range("A1") kopierst du alles mit nur einer Zeile Code. Das Zielblatt wird automatisch ans Ende deiner Arbeitsmappe verschoben.

Teil 5: Wie du das Makro testest

  1. Erstelle eine Beispieltabelle mit Daten in Spalte A und B.
  2. Gehe zurück zum VBA-Editor.
  3. Klicke irgendwo in den Code und drücke F5 oder das grüne "Play"-Symbol.
  4. Füge danach weitere Zeilen zu deiner Tabelle hinzu und lass das Makro erneut laufen. Du wirst sehen, dass die Summe und Formatierung automatisch nach unten rücken.

Profi-Tipp: Relative Bezüge vermeiden

Wenn du Makros mit dem "Makro-Rekorder" aufzeichnest, nutzt Excel oft feste Zellbezüge. Lerne stattdessen, die Variable lastRow zu nutzen – das ist der Unterschied zwischen einem Anfänger-Skript und einem professionellen Tool!

0