Excel VBA – Arbeitsbereich (Range) definieren

In diesem Beitrag zeige ich Ihnen, wie Sie einen Bereich (Range) in VBA festlegen können.

111 VBA Makros die Ihr Leben leichter machen. Egal ob Sie Einsteiger oder fortgeschrittener Anwender sind – in diesem Buch finden Sie Lösungen für Ihre alltäglichen Aufgaben. Keine Vorkenntnisse notwendig.

Dieser Beitrag ist ein Teil der VBA Grundlagen Serie.

Als erstes fangen wir mit dem Arbeitsblatt (Worksheet) als übergeordneten Element an. In unserem Beispiel hat die Excel Mappe zwei Arbeitsblätter: „Ist“ und „Plan“.

Zunächst können Sie einen Bereich über den Namen (Beschreibung) des Worksheets referenzieren. Dies ist der Name den Sie auch in den Excel Reitern sehen.

Der folgende Code färbt die Zellen von A1 bis C10 im Worksheet „Ist“ gelb:

Sub Beispiel01()
'Referenziere Worksheet über Namen
 With Worksheets("Ist").Range("A1:C10")
 .Interior.Color = RGB(255, 255, 0)
 End With
End Sub

Sie können auch den technischen Namen (CodeName) für die Referenznutzen. Während ein normaler Benutzer den Namen (Beschreibung) leicht ändern kann, muss der technische Name im VBA Projekt geändert werden.

Das folgende Makro färbt den selben Bereich in rot. Allerdings erfolgt in diesem Fall die Refernzierung über den technischen Namen. In unserem Beispiel ist es die „Tabelle1“.

Sub Beispiel02()
'Referenziere Worksheet über technischen Namen (CodeName)
 With Tabelle1.Range("A1:C10")
 .Interior.Color = RGB(255, 0, 0)
 End With
End Sub

Eine andere Möglichkeit besteht darin, das Arbeitsblatt über den Index zu definieren. Das folgende Makro färbt die Zellen A1 bis C10 des ersten Arbeitsblattes blau ein.

Sub Beispiel03()
'Referenziere Worksheet über Index
'sollte nicht benutzt werden, da sich die Reihenfolge jederzeit ändern kann
 With Worksheets(1).Range("A1:C10")
 .Interior.Color = RGB(0, 0, 255)
 End With
End Sub

Bereich des ersten Arbeitsblattes blau eingefärbt

Allerdings sollten Sie beachten, dass die Reihenfolge der Arbeitsblätter sich jederzeit ändern kann. Dies könnte zu unerwarteten Ergebnissen führen, wie das folgende Bild zeigt.

Nachdem Sie nun die Grundlagen beherrschen und das Prinzip verstanden haben, stelle ich einen anderen Ansatz vor. Dabei definieren wir zunächst den Bereich über das Set Kommando, um dann anschließend über With mit diesem Bereich zu arbeiten.

Das folgende Makro nutzt den Namen (Beschreibung) des Arbeitsblattes:

Sub Beispiel04()
'Range manuell definieren mit Worksheet Namen
Dim rngMeinBereich As Range
Set rngMeinBereich = Worksheets("Plan").Range("A1:C10")

 With rngMeinBereich
 .Interior.Color = RGB(255, 255, 0)
 End With
End Sub

Range manuell definieren mit Worksheet Namen

Selbstverständlich können Sie auch den technischen Namen (CodeName) zur Referenzierung verwenden.

Sub Beispiel05()
'Range manuell definieren mit Worksheet technischen Namen
Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle2.Range("A1:C10")

 With rngMeinBereich
 .Interior.Color = RGB(255, 0, 0)
 End With
End Sub

Definierter Bereich wird rot eingefärbt.

Dabei kann die Range aus einer einzigen Zelle bestehen:

Sub Beispiel06()
'Range aus einer einzigen Zelle
Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle2.Range("A1")

 With rngMeinBereich
 .Interior.Color = RGB(0, 255, 0)
 End With
End Sub

Oder auch aus einer einzigen Spalte:

Sub Beispiel07()
'Range aus einer einzigen Spalte
Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle2.Range("A:A")

 With rngMeinBereich
 .Interior.Color = RGB(0, 255, 255)
 End With
End Sub

Beziehungsweise aus einer einzigen Zeile:

Sub Beispiel08()
'Range aus einer einzigen Zeile
Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle2.Range("1:1")

 With rngMeinBereich
 .Interior.Color = RGB(0, 0, 255)
 End With
End Sub

Wenn ein Name für einen Bereich definiert wurde, können Sie auch über diesen Namen auf den Bereich zugreifen. Sie können den Namen direkt im Namensfenster vergeben oder über Formeln -> Namensmanager (Tastenkombination STRG+F3).

Das folgende Makro färbt den Bereich, der mit dem Namen „Bericht“ definiert wurde, schwarz.

Sub Beispiel09()
'Range die über ein Namenfeld definiert wurde. 
'Siehe auch Namensmanager in "Formeln" Ribbon
Dim rngMeinBereich As Range
Set rngMeinBereich = Range("Bericht")

 With rngMeinBereich
 .Interior.Color = RGB(0, 0, 0)
 End With
End Sub

Es ist auch möglich mehrere Bereiche auf einmal zu selektieren. Alternativ können Sie auch einzelne Bereiche auswählen und über den Befehl Union verknüpfen. Mit dem folgenden Code definieren Sie die Zellen B2 bis D7 sowie C10 bis G11 als Arbeitsbereich und färben diese in hellblau.

Sub Beispiel10()
'Mehrere Ranges auswählen
'alternativ auch einzeln definieren und Union (range1, range2, ...) verwenden
Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle1.Range("B2:D7,C10:G11")

 With rngMeinBereich
 .Interior.Color = RGB(0, 255, 255)
 End With
End Sub

VBA Mkros die Ihre Leben leichter machen

111 VBA Makros die Ihr Leben leichter machen

Wollen Sie Ihre langweiligen Routineaufgaben loswerden? Möchten Sie Excel an Ihre Bedürfnisse anpassen, Berechnungen automatisieren, eigene Dialogfelder erstellen und vieles mehr? Dieses Buch zeigt Ihnen, wie Sie Ihre Aufgaben schnell und einfach mit VBA erledigen.

Hört sich gut an

Einen komplett anderen Ansatz stellt die Definition des Bereiches über die Cells Eigenschaft (Property) dar. Mit dieser Eigenschaft definieren Sie den relevanten Bereich anhand von Zeilen- und Spaltenindex. Dabei steht die erste Spalte für Spalte A, die zweite Spalte für Spalte B und so weiter.

Schauen Sie sich das folgende Makro an. Dieses wählt die Zelle in der ersten Zeile und der zweiten Spalte aus. Also B1.

Sub Beispiel11()
'Range über Cells Eigenschaft des Worksheets (Property) definieren
'Cells(row,column)

Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle1.Cells(1, 2)

 With rngMeinBereich
 .Interior.Color = RGB(0, 0, 255)
 End With
End Sub

Obwohl Sie auch Range("B1") nutzen können, um die Zelle B1 auszuwählen, könnte in manchen Fällen die Cells-Eigenschaft besser geeignet sein, weil Sie Variablen für Zeilen oder Spalten verwenden können.

Sub Beispiel12()
'Dasselbe Ergebnis wie über Cells(row,column)

Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle1.Range("B1")

 With rngMeinBereich
 .Interior.Color = RGB(0, 0, 0)
 End With
End Sub

Mithilfe der Cells Eigenschaft können Sie auch einen Bereich anhand mehrerer Zellen definieren. Das nachfolgende Makro definiert einen Bereich zwischen der ersten Zeile und erster Spalte (A1) sowie zehnter Zeile und achter Spalte (H10). Anschliessend wird dieser Bereich rot eingefärbt.

Sub Beispiel13()
'Range anhand mehrerer Cells definieren
'Cells(row,column)

Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle1.Range(Cells(1, 1), Cells(10, 8))

 With rngMeinBereich
 .Interior.Color = RGB(255, 0, 0)
 End With
End Sub

Bereich aus mehreren Zellen über Cells definiert.

Das Ergebnis entspricht der folgenden Schreibweise mit Range:

Sub Beispiel14()
'Dasselbe Ergebnis mit Range

Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle1.Range("A1:H10")

 With rngMeinBereich
 .Interior.Color = RGB(255, 255, 0)
 End With
End Sub

Allerdings, wie bereits erwähnt, können Sie Variablen für Zeilen und Spalten verwenden. So können Sie Ihre Range flexibel gestalten. Zum Beispiel, um den Bereich auf letzte Zeile oder letzte Spalte festzulegen.

Betrachten wir das folgende Beispiel. Unser Arbeitsblatt enthält einen Bericht bei dem nur die Datenzellen E3 bis H10 relevant sind. Diese wählen wir als unseren Arbeitsbereich aus.

Sub Beispiel15()
'Range anhand mehrerer Cells definieren
'Beispiel mit Daten
'Cells(row,column)

Dim rngMeinBereich As Range
Set rngMeinBereich = Tabelle2.Range(Cells(3, 5), Cells(10, 8))

 With rngMeinBereich
 .Interior.Color = RGB(255, 0, 0)
 End With
End Sub

Die relevanten Zellen werden rot eingefärbt.

Nun kann es aber sein, das sich die Anzahl der relevanten Zeilen ändert. Daher wollen wir den Bereich anhand der letzten Zeile flexibel definieren.

Sub Beispiel16()
'Range flexibel definieren
'Anfang bekannt, letzte Spalte bekannt, letzte Zeile unbekannt
'Suche letzte Zeile in Spalte H
'Cells(row,column)
Dim rngMeinBereich As Range
Dim lngLetzteZeile As Long

With Tabelle2
    lngLetzteZeile = .Range("H" & .Rows.Count).End(xlUp).Row
'Beachten Sie, dass bei diesem Ansatz alle ausgeblendeten Zeilen übersprungen werden.
End With

Set rngMeinBereich = Tabelle2.Range(Cells(3, 5), Cells(lngLetzteZeile, 8))

 With rngMeinBereich
 .Interior.Color = RGB(255, 0, 0)
 End With
End Sub

Alternative Schreibweise mit der Cells Eigenschaft könnte wie folgt aussehen:

Sub Beispiel17()
'Range flexibel definieren
'Anfang bekannt, letzte Spalte bekannt, letzte Zeile unbekannt
'Suche letzte Zeile in Spalte 8
'Alternative Schreibweise, Spalte 8 = H
'Cells(row,column)
Dim rngMeinBereich As Range
Dim lngLetzteZeile As Long

With Tabelle2
    lngLetzteZeile = .Cells(Rows.Count, 8).End(xlUp).Row
'Beachten Sie, dass bei diesem Ansatz alle ausgeblendeten Zeilen übersprungen werden.
End With

Set rngMeinBereich = Tabelle2.Range(Cells(3, 5), Cells(lngLetzteZeile, 8))

 With rngMeinBereich
 .Interior.Color = RGB(255, 255, 0)
 End With
End Sub

Relevanten Bereich über VBA Cells Eigenschaft definieren

Dabei ermittelt der Befehl lngLetzteZeile = .Cells(Rows.Count, 8).End(xlUp).Row die erste Zeile von unten, die gefüllt ist. Cells verweist auf die Zellen, die Funktion Rows.Count gibt die Anzahl der Zeilen im Tabellenblatt zurück. 8 ist die Spalte, in der gesucht werden soll. Die Spalte 8 entspricht der Spalte H. .End weist Excel an, dass es von unten zählen soll. xlUp ist die Richtung in der gesucht werden soll. Es funktioniert so, als ob Sie in die letzte Zeile der Spalte H gehen und die Tastenkombination STRG+Pfeil nach oben drücken. Schliesslich gibt .Row die erste Zeile von unten zurück, die gefüllt ist.

Allerdings sollten Sie beachten, dass bei diesem Ansatz alle ausgeblendeten Zeilen übersprungen werden. Zuverlässiger ist die Methode Find:

Sub Beispiel19()
'Range flexibel definieren
'Anfang bekannt, letzte Spalte bekannt, letzte Zeile unbekannt
'Suche letzte Zeile
'Cells(row,column)
Dim rngMeinBereich As Range
Dim lngLetzteZeile As Long

With Tabelle2
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
'Dies ist erforderlich, denn wenn es keine Zellen mit Daten im Arbeitsblatt gibt .Find einen Run Time Error 91: Object Variable or With block variable not set
        lngLetzteZeile = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row

    Else
        lngLetzteZeile = 1
    End If
End With

Set rngMeinBereich = Tabelle2.Range(Cells(3, 5), Cells(lngLetzteZeile, 8))

 With rngMeinBereich
 .Interior.Color = RGB(0, 255, 0)
 End With
End Sub

Letzte Zeile über VBA Find identifiziert

Sie können diesen Ansatz auch nutzen, um die letzte Zeile als Range zu übergeben. Dabei müssen Sie die Spalte H und die letzte Zeile über & verketten. Das Makro würde wie folgt aussehen:

Sub Beispiel20()
'Range flexibel definieren
'Range statt Cells Schreibweise mit Zeichenverkettung
'Anfang bekannt, letzte Spalte bekannt, letzte Zeile unbekannt
'Suche letzte Zeile
Dim rngMeinBereich As Range
Dim lngLetzteZeile As Long

With Tabelle2
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
'Dies ist erforderlich, denn wenn es keine Zellen mit Daten im Arbeitsblatt gibt .Find einen Run Time Error 91: Object Variable or With block variable not set
        lngLetzteZeile = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row

    Else
        lngLetzteZeile = 1
    End If
End With

Set rngMeinBereich = Tabelle2.Range("E3:H" & lngLetzteZeile)
'übrigens, wenn Sie beide Elemente verketten wollen, würde die Syntax so aussehen
'Set rngMeinBereich = Tabelle2.Range("E" & lngLetzteZeile & ":H" & lngLetzteZeile)
 
 With rngMeinBereich
 .Interior.Color = RGB(0, 0, 0)
 End With
End Sub

Der identifizierte Bereich wird schwarz eingefärbt.

Über Find identifizierte Bereich wird schwarz eingefärbt.

Die Find Methode können Sie auch verwenden, um die letzte Spalte zu finden. Dies ist nützlich, falls nicht nur die Anzahl der Zeilen, sondern auch die Anzahl der Spalten sich ändern kann.

Sub Beispiel21()
'Range flexibel definieren
'Anfang bekannt, letzte Spalte unbekannt, letzte Zeile unbekannt
'Suche letzte Zeile
'Suche letzte Spalte
'Cells(row,column)
Dim rngMeinBereich As Range
Dim lngLetzteZeile As Long
Dim lngLetzteSpalte As Long

With Tabelle2
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
'Dies ist erforderlich, denn wenn es keine Zellen mit Daten im Arbeitsblatt gibt .Find einen Run Time Error 91: Object Variable or With block variable not set
        lngLetzteZeile = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
                      
        lngLetzteSpalte = .Cells.Find(What:="*", _
                        After:=.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column

    Else
        lngLetzteZeile = 1
        lngLetzteSpalte = 1
    End If
End With

Set rngMeinBereich = Tabelle2.Range(Cells(3, 5), Cells(lngLetzteZeile, lngLetzteSpalte))

 With rngMeinBereich
 .Interior.Color = RGB(0, 255, 255)
 End With
End Sub

Letzte Zeile und letzte Spalte mit VBA finden

Ist die letzte Zeile und Spalte bekannt, können Sie aus diesen Koordinaten auch die letzte Zelle ableiten. Das Makro dazu sieht wie folgt aus:

Sub Beispiel22()
'Range flexibel definieren
'Range statt Cells Schreibweise mit Zeichenverkettung
'Anfang bekannt, letzte Spalte unbekannt, letzte Zeile unbekannt
'Suche letzte Zeile
'Suche letzte Spalte

Dim rngMeinBereich As Range
Dim lngLetzteZeile As Long
Dim lngLetzteSpalte As Long
Dim strletzteZelle As String

With Tabelle2
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
'Dies ist erforderlich, denn wenn es keine Zellen mit Daten im Arbeitsblatt gibt .Find einen Run Time Error 91: Object Variable or With block variable not set
        lngLetzteZeile = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
                      
        lngLetzteSpalte = .Cells.Find(What:="*", _
                        After:=.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column

    Else
        lngLetzteZeile = 1
        lngLetzteSpalte = 1
    End If
End With

strletzteZelle = Replace(Cells(lngLetzteZeile, lngLetzteSpalte).Address, "$", vbNullString)

Set rngMeinBereich = Tabelle2.Range("E3:" & strletzteZelle)

 With rngMeinBereich
 .Interior.Color = RGB(0, 0, 255)
 End With
End Sub

Nun kennen Sie die verschiedenen Möglichkeiten, einen Arbeitsbereich zu definieren. Typischerweise werden Sie diesen nutzen, um alle Zellen darin zu verarbeiten. Wie Sie dabei ganz einfach feststellen können, welche Zelle gerade verarbeitet wird, zeige ich im nächsten Beitrag.

Die Beispiel Excel Datei mit dem sofort einsetzbaren Makro und 111+ andere Makros, die Ihr Leben leichter machen, finden Sie hier.

Beeindrucke deinen Chef mit coolen Excel Tricks!

Lasse dich von deinen Kollegen nicht abhängen!
Spare Zeit und nutze diese für angenehmere Dinge
Steigere deine Produktivität und Effizienz
Es ist leicht - In meinem Newsletter gebe ich Schritt für Schritt Anleitungen die jeder versteht
Bei der Anmeldung zu meinem Newsletter erhälst du das Buch „Excel Tricks die deinen Chef beeindrucken“ als Willkommensgeschenk.