VBA Offset in Excel — Relativ zu einer Zelle bewegen (Offset gegen Resize und die Off-by-One-Falle)

TL;DR — rng.Offset(rowDelta, colDelta) gibt einen neuen Bereich zurück, der um so viele Zeilen und Spalten von rng verschoben ist. Es ist ein Schritt, kein Ort: Es behält dieselbe Größe, ändert rng nicht und wählt nichts aus. Die beiden Zahlen sind relativ — Offset(1, 0) ist eine Zeile nach unten, Offset(0, 0) ist die Zelle selbst, und negative Werte gehen nach oben/links. Offset bewegt; Resize ändert die Größe. Kombinieren Sie beide, und Sie erhalten das nützlichste Bereichs-Idiom in VBA: data.Offset(1).Resize(data.Rows.Count - 1) — „alles außer der Überschrift".

Offset ist die Art, wie ein Makro sagt „die Zelle neben der, die ich gerade gefunden habe". Sie haben einen Treffer, eine Summe, eine Überschrift lokalisiert — jetzt wollen Sie die Zelle eine Spalte weiter oder eine Zeile tiefer lesen oder beschreiben. Diese relative Bewegung ist die gesamte Aufgabe von Offset, und sie geht in Fleisch und Blut über, sobald Sie aufhören, die Argumente als Koordinaten zu lesen, und anfangen, sie als Schritte zu lesen.

Was Sie lernen

Das mentale Modell: Offset ist ein relativer Schritt, keine absolute Adresse
Die Off-by-One-Falle — warum Offset(1, 1) auf B2 landet, nicht auf A1
Offset behält die Größe; Resize ändert sie — und warum Sie meist beide brauchen
Das Kopfzeilen-Überspring-Idiom Offset(1).Resize(n - 1), erklärt
Wie sich Range.Offset (VBA) von der OFFSET()-Tabellenfunktion unterscheidet

Das mentale Modell: Offset ist ein Schritt, kein Ort

Stellen Sie sich auf eine Zelle. Offset(rowsDown, colsRight) macht so viele Schritte und reicht Ihnen die Zelle (oder den Block), auf der Sie landen — einen brandneuen Bereich. Das Original bleibt unangetastet. Weil die Bewegung relativ ist, bedeutet dasselbe Offset(1, 0) „eine Zeile nach unten", egal ob Sie bei A1 oder Z500 starten.

Sub OffsetSteps()
    Debug.Print Range("B2").Offset(1, 0).Address   ' -> $B$3   (eine Zeile nach unten)
    Debug.Print Range("B2").Offset(0, 1).Address   ' -> $C$2   (eine Spalte nach rechts)
    Debug.Print Range("B2").Offset(-1, 0).Address  ' -> $B$1   (eine Zeile nach oben)
    Debug.Print Range("B2").Offset(0, 0).Address   ' -> $B$2   (keine Bewegung)
End Sub

Beachten Sie: Offset(0, 0) ist die Zelle selbst. Das ist das verräterische Zeichen dafür, dass es sich um Deltas handelt, nicht um Positionen. Wenn Sie eine absolute Zelle wollen, berechnen Sie kein Offset von A1 aus — nehmen Sie Cells(row, col). Offset ist für „relativ zu etwas, das ich bereits habe".

Die Regel, die Anfänger erwischt: die Off-by-One-Falle

Der Offset-Bug Nummer eins ist, die Argumente als Koordinate zu lesen. Offset(1, 1) bedeutet nicht Zelle A1 oder „Zeile 1, Spalte 1". Es bedeutet eine Zeile nach unten und eine Spalte nach rechts vom Anker. Von A1 aus landet das auf B2.

Range("A1").Offset(1, 1).Address   ' -> $B$2, NICHT $A$1
Range("A1").Offset(0, 0).Address   ' -> $A$1

Sagen Sie die beiden Argumente jedes Mal laut als „runter, rechts" — und der Bug verschwindet. Das erklärt auch das klassische Schleifenmuster: Um ein Ergebnis neben jede gefundene Zeile zu schreiben, versetzen Sie um den Schleifenzähler, und die erste Zeile nutzt Offset(0, …), nicht Offset(1, …).

Der Unterschied, über den alle stolpern: Offset bewegt, Resize ändert die Größe

Offset ändert nie, wie groß ein Bereich ist — es verschiebt die gesamte Form. Bewegen Sie eine Spalte aus zehn Zellen, erhalten Sie eine Spalte aus zehn Zellen an anderer Stelle:

Range("A1:A10").Offset(0, 1).Address   ' -> $B$1:$B$10   (10 Zellen, nach rechts verschoben)

Um die Größe eines Bereichs zu ändern, brauchen Sie eine andere Methode, Resize(rows, cols), die den Block von seiner oberen linken Zelle aus neu zeichnet:

Range("A1").Resize(5, 2).Address       ' -> $A$1:$B$5   (5 Zeilen, 2 Spalten)

Sie sind die beiden Hälften der Bereichsarithmetik: Offset wählt wo, Resize wählt wie groß. Sie greifen ständig zu beiden zusammen — am häufigsten, um eine Kopfzeile zu entfernen.

Das Idiom, das Sie sich merken sollten: Kopfzeile überspringen mit Offset + Resize

Sie haben eine Tabelle samt Überschrift und wollen nur den Datenrumpf. Versetzen Sie eine Zeile nach unten, um von der Überschrift wegzukommen — aber Offset allein behält dieselbe Höhe, sodass der Bereich nun eine Zeile über die Daten hinausreicht. Resize schrumpft ihn um genau diese eine Zeile zurück:

Sub DataBodyOnly()
    Dim tbl As Range, body As Range
    Set tbl = Range("A1").CurrentRegion          ' Überschrift + Daten
    Set body = tbl.Offset(1).Resize(tbl.Rows.Count - 1)
    body.Select                                  ' nur Datenzeilen, keine Überschrift
End Sub

Offset(1) (das Spalten-Delta ist standardmäßig 0) schiebt den ganzen Block eine Zeile nach unten; Resize(tbl.Rows.Count - 1) kappt die nun überstehende unterste Zeile. Das Ergebnis ist der Datenrumpf ohne Überschrift — genau der Bereich, den Sie sortieren, kopieren oder löschen wollen. Diese eine Zeile ersetzt viel manuelles Zusammenbauen von A2:A-Strings. Um tbl ohne festes Verdrahten zu finden, siehe die letzte Zeile finden und CurrentRegion.

Die Falle am Blattrand

Weil Offset relativ ist, ist ein Schritt über den Rand des Arbeitsblatts hinaus ein Fehler, keine stille Begrenzung:

Range("A1").Offset(-1, 0)   ' Laufzeitfehler 1004 — kann nicht über Zeile 1 hinaus

Wenn ein Makro nahe der oberen linken Ecke nach oben oder links läuft (häufig beim Absuchen nach Überschriften), sichern Sie die Bewegung ab oder prüfen Sie rng.Row > 1, bevor Sie nach oben versetzen.

Offset (die Methode) gegen OFFSET (die Tabellenfunktion)

Sie teilen sich einen Namen und dieselbe Idee, aber sie sind nicht dasselbe Werkzeug. Die Tabellenfunktion OFFSET() wohnt in einer Zelle, ist volatil (sie berechnet sich bei jeder Änderung neu, was eine Arbeitsmappe verlangsamen kann) und gibt einen Wert oder Bezug für eine Formel zurück. Range.Offset ist eine VBA-Methode: Sie gibt ein Range-Objekt zurück, ist nicht volatil und läuft nur, wenn Ihr Code läuft. Wenn Sie ein Makro schreiben, nehmen Sie die Methode; greifen Sie nur dann zur Tabellenfunktion, wenn Sie einen lebendigen, selbstaktualisierenden Bezug innerhalb einer Formel brauchen.

Wie ExcelMaster hilft

Offset-lastiger Code baut meist eine einzige Sache: ein Ergebnis, das relativ zu etwas Gefundenem platziert wird — eine Markierung neben jedem Treffer, eine laufende Summe eine Spalte weiter, ein Wert aus der Zeile unter einer Beschriftung. ExcelMaster nimmt diese Absicht in einfachem Deutsch entgegen — „Schreibe neben jede überfällige Rechnung die Tage Verzug" — und erzeugt die Logik der relativen Bezüge, sodass Sie keine Deltas von Hand abzählen oder einen Off-by-One debuggen.

Sie werden Offset weiterhin in einer engen Makroschleife schreiben. Aber für die alltägliche „Setze ein Ergebnis neben jede Zeile"-Aufgabe ist das Beschreiben der Platzierung schneller und schwerer falsch zu machen als das Nachdenken über (1, 0) gegen (0, 1).

Häufig gestellte Fragen

Was macht Offset in VBA?

rng.Offset(rowDelta, colDelta) gibt einen neuen Bereich zurück, der um so viele Zeilen und Spalten von rng verschoben ist und dieselbe Größe behält. Es ist eine relative Bewegung: Offset(1, 0) ist eine Zeile nach unten, Offset(0, 1) ist eine Spalte nach rechts, und negative Zahlen bewegen nach oben oder links. Es ändert oder wählt den ursprünglichen Bereich nicht aus.

Warum gibt Offset(1, 1) nicht Zelle A1 zurück?

Weil die Argumente Deltas sind, keine Koordinaten. Offset(1, 1) bedeutet eine Zeile nach unten und eine Spalte nach rechts von der Ankerzelle, also ist Range("A1").Offset(1, 1) gleich B2. Nehmen Sie Cells(row, col), wenn Sie eine absolute Zelle statt eines relativen Schritts wollen.

Was ist der Unterschied zwischen Offset und Resize?

Offset bewegt einen Bereich, ohne seine Größe zu ändern; Resize(rows, cols) ändert die Größe von der oberen linken Zelle aus, ohne ihn zu bewegen. Sie kombinieren beide oft — rng.Offset(1).Resize(rng.Rows.Count - 1) überspringt eine Kopfzeile, indem es eine Zeile nach unten geht und um eine schrumpft.

Wie überspringe ich die Kopfzeile mit Offset?

Nehmen Sie table.Offset(1).Resize(table.Rows.Count - 1). Offset(1) schiebt den Block nach unten über die Überschrift hinaus; Resize kappt die Zeile, die nun unten übersteht, sodass nur der Datenrumpf bleibt.

Ist VBA Range.Offset dasselbe wie die OFFSET-Tabellenfunktion?

Nein. Range.Offset ist eine VBA-Methode, die ein Range-Objekt zurückgibt und nicht volatil ist. Die OFFSET()-Tabellenfunktion wohnt in einer Zelle, ist volatil (berechnet sich ständig neu) und gibt einen Wert oder Bezug für Formeln zurück. Nehmen Sie die Methode in Makros; nehmen Sie die Funktion nur für lebendige Formelbezüge.

Getestet in

Getestet in: Excel 365 (Windows 11), VBA 7.1 — zuletzt geprüft am 30.07.2026.

Verwandte Anleitungen: VBA Letzte Zeile · VBA UsedRange vs. CurrentRegion · VBA Range · OFFSET-Tabellenfunktion