Excel VBA - Arbeitsbereich (Range) flexibel 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“.

Arbeitsblätter der Excel Mappe

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.

Reiter einer Excel Mappe

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
Festgelegter Bereich gelb eingefärbt

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.

Technischer Name (CodeName) der Tabelle

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
Definierter Bereich rot eingefärbt

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.

Reihenfolge der Blätter vertauscht

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
Eine Zelle als Range festlegen

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
Range aus einer einzigen Spalte

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
Range aus einer einzigen Zeile

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).

Namensfeld

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
Bericht schwarz eingefärbt

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
Zwei separete Bereiche ausgewählt

Unternehmensplanung mit SAP BPC

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.

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
Zelle über Range selektiert

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
Dasselbe Ergebnis mit Range

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.

Bericht mit Daten
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.

Relevanter Bereich 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
Letzte Zelle mit VBA finden

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.
20 Excel Tricks

Jetzt anfordern!

* Pflichtfeld
 
Kein SPAM. 100% sicher.

0 Kommentare

Hinterlasse ein Kommentar

An der Diskussion beteiligen?
Hinterlasse uns deinen Kommentar!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.