Translate

Dienstag, 17. Februar 2015

EXCEL: Zellenbereich unterhalb der aktiven Zelle bis Tabellenende auswählen



Jeder Excel-Anwender steht häufig vor der Aufgabe alle Zellen vertikal bis Tabellenende ausgehend von der aktiven Zelle zu markieren oder diesen Bereich mittels VBA bearbeiten zu wollen.
Hinweis: Mit Tabellenende ist nicht die letzte gefüllte Zelle im Arbeitsblatt gemeint, sondern die letzte Zeile einer Datenliste.
Die bekannte Tastenkombination [Umschalt] + [Strg] + [PfeilNachUnten] ist leider nicht immer hilfreich, da diese bei Leer-Zellen endet und nicht zwangsläufig bei Tabellenende.
Die Tastenkombination [Strg]+[a] findet zuverlässig das Tabellenende (insofern keine durchgehende Leerzeile sich in der Tabellenliste sich befindet, wo von ich ausgehe).
Leider ist dann die gesamte Tabelle ausgewählt und nicht der eigentliche gewünschter Bereich.

Daher schreiben wir uns schnelle die folgende VBA-Prozedur:
Public Function CurrentRegionDownward(Optional StartupRange As Range _
                           , Optional SelectRange As Boolean = True) As Range
    'Copyright (r) by Jean Pierre Allain
    Dim t As Range, r As Long
   
    'Wenn kein StartupRange angegeben wurde, dann wird die Aktive Zelle verwendet
    If StartupRange Is Nothing Then Set StartupRange = ActiveCell
   
    'Der StartupRange wird auf eine Zelle reduziert
    Set StartupRange = Cells(StartupRange.Row, StartupRange.Column)
   
    'Die Methode CurrentRegion wählt den gesamten zusammenhängenden Bereich
    'in einer Liste. Dieser wird in t gespeichert
    Set t = StartupRange.CurrentRegion
   
    'Die letzte Zeile aus t wird ausgelesen und in r gespeichert
    r = t.Row + t.Rows.Count - 1
   
    'Nun wird der Bereich von StartupRange bis zum Tabellenende gespeichert
    Set StartupRange = Range(StartupRange, Cells(r, StartupRange.Column))
   
    'Wenn SelectRange=True, dann wird der ermittelte Bereich
    'markiert/ausgewählt/selektiert
    If SelectRange Then StartupRange.Select
   
    'Die Funktion liefert als Rückgabewert den ermittelte Bereich
    'als Range-Objekt zurück
    Set CurrentRegionDownward = StartupRange
End Function

Um den zuvor beschriebenen Bereich zu markieren, muss lediglich diese Funktionsprozedur ausgeführt werden.
Diese Funktionalität kann schnell und einfach verfügbar gemacht werden, in dem sie in die Schnellzugriffsleiste oder in einem Menüband zugeordnet wird.

Hierfür benötige wir asllerdings noch einen kleinen Trick, den bei der Makrozuweisung listet Excel leider nur Sub-Prozeduren ohne Übergabeparameter auf.
Daher tricksen wir Excel aus, in dem wir kurzfristig unsere Prozedur in einer Sub-Prozedur umwandeln:
Public Sub CurrentRegionDownward()
‘Public Function CurrentRegionDownward(Optional StartupRange As Range
                       _, Optional SelectRange As Boolean = True) As Range

Nun lässt sich unsere Prozedur problemlos auf eine Schaltflächensymbol in der Schnellzugriffsleiste oder ins Menüband zuweisen.
Anschließend bitte nicht vergessen, die Sub-Prozedur wieder in eine Funktionsprozedur umzuwandeln:
‘Public Sub CurrentRegionDownward()
Public Function CurrentRegionDownward(Optional StartupRange As Range
                       _, Optional SelectRange As Boolean = True) As Range


Diese Lösung kann auch sehr einfach und effizient in der VBA-Programmierung eingesetzt werden.
Beispiele:
CurrentRegionDownward(, False).FillDown
‘Kopiert den Wert in der aktiven Zelle bis zum Tabellenende (Ohne den Zielbereich zu markieren)

CurrentRegionDownward(Range("A10")).FillDown
‘Kopiert den Wert in der Zelle A10 bis zum Tabellenende (dabei wird der Zielbereich markiert)

Mit dieser kleinen Funktion wünsche ich alle eine Große Arbeitserleichterung.

2 Kommentare:

  1. Ich dachte es geht darum bis zur letzten verwendeten Zeile zu markieren?
    Ich hab mir 2 Funktionen geschrieben die mir die letzte verwendete Zeile oder Spalte zurück geben. Egal ob Leerzellen dazwischen sind.

    ' -------------------------------------------------------------------
    ' Ermittelt die letzte verwendete Zeile einer Spalte
    ' wird keine Spalte angegeben so wird Spalte 1 = A verwendet
    Function Get_Last_Row(Optional vSpalte As Variant = 1, Optional wsh As Worksheet) As Long
    ' WE 11-07-28

    If wsh Is Nothing Then Set wsh = ActiveSheet
    If IsNumeric(vSpalte) = True Then
    Get_Last_Row = wsh.Cells(Columns(CInt(vSpalte)).Cells.Count, CInt(vSpalte)).End(xlUp).Row
    Else
    Get_Last_Row = wsh.Cells(Columns(vSpalte).Cells.Count, vSpalte).End(xlUp).Row
    End If

    End Function

    ' -------------------------------------------------------------------
    ' Ermittelt die letzte verwendete Spalte einer Zeile
    ' wird keine Zeile angegeben so wird Zeile 1 verwendet
    Function Get_Last_Col(Optional lngZeile As Long = 1, Optional wsh As Worksheet) As Long
    ' WE 11-08-03

    If wsh Is Nothing Then Set wsh = ActiveSheet
    Get_Last_Col = wsh.Cells(lngZeile, Rows(lngZeile).Cells.Count).End(xlToLeft).Column

    End Function

    AntwortenLöschen
  2. Vielen dank für den Hinweis, damit habe ich die Anforderung jetzt nochmal präzisiert.
    Mit Tabellenende ist das Ende einer Tabelle gemeint und eine Tabelle kann man auch als Datenliste/Datenbank bezeichnen. Es ist eine Liste mit einen Zusammenhängenden gefüllten Bereich. Und das Ende dieser Liste gilt es heraus zu finden.

    AntwortenLöschen