Tipp 0342 Letzte Zeile, Spalte, Zelle mit Daten ermitteln
Autor/Einsender:
Datum:
  Angie
07.01.2006 (Update)
Entwicklungsumgebung:   Excel 2000
In Excel gibt es keine integrierte Methode, mit der man die letzte Zeile, Spalte oder Zelle, die Daten enthält, zuverlässig ermitteln kann. Die SpecialCells-Methode in Verbindung mit der Konstante xlCellTypeLastCell berücksichtigt leider nicht nur Daten, sondern auch ggf. vorhandene Formatierungen der Zellen. Dies trifft auch auf die UsedRange-Eigenschaft zu.
Wenn zum Beispiel in einem zunächst leeren Tabellenblatt in der Zelle C3 ein Datum eingegeben und anschließend mit der Taste [Entf] gelöscht wird, wird bei Ausführung des Dialogs GeheZu... mit der Option 'Letzte Zelle' (Menü / Bearbeiten / GeheZu... / Inhalte...) die Zelle C3 ausgewählt (VBA: Selection.SpecialCells(xlCellTypeLastCell).Select), da diese Zelle nach wie vor als Datum formatiert ist, obwohl der Inhalt der Zelle gelöscht wurde. Manuell könnte man über das Menü / Bearbeiten / Löschen / Alles sowohl den Inhalt als auch ggf. vorhandene Formatierungen löschen. Trotzdem würde zunächst als 'Letzte Zelle' die Zelle C3 ausgewählt werden. Nur ein anschließendes Speichern der Arbeitsmappe liefert dann das "richtige" Ergebnis.
Verwendeter Bereich mit Daten ermitteln
Wenn in einem Tabellenblatt der tatsächlich verwendete Bereich, also inkl. formatierter Zellen, die evtl. keine Daten enthalten, ermittelt werden soll, kann die SpecialCells-Methode oder die UsedRange-Eigenschaft verwendet werden. Soll jedoch nur der verwendete Bereich, der Daten (Konstanten und Formeln) enthält, ermittelt werden, so könnte die folgende Funktion ein Lösungsansatz sein.
 
Public Function GetDataRange(ByRef objWks As Worksheet) As Range
  Dim objUsedRange As Range
  Dim objAreaRange As Range

  Dim nFirstColA   As Integer
  Dim nFirstRowA   As Long
  Dim nLastColA    As Integer
  Dim nLastRowA    As Long

  Dim nFirstCol    As Integer
  Dim nFirstRow    As Long
  Dim nLastCol     As Integer
  Dim nLastRow     As Long

  On Error Resume Next

  If Application.WorksheetFunction.CountA(objWks.Cells) > 0 Then
    Set objUsedRange = Union( _
          objWks.UsedRange.SpecialCells(xlCellTypeConstants), _
          objWks.UsedRange.SpecialCells(xlCellTypeFormulas))

    If objUsedRange Is Nothing Then
      Set objUsedRange = _
            objWks.UsedRange.SpecialCells(xlCellTypeConstants)
    End If

    If objUsedRange Is Nothing Then
      Set objUsedRange = _
            objWks.UsedRange.SpecialCells(xlCellTypeFormulas)
    End If

    If Not objUsedRange Is Nothing Then
       nFirstCol = objWks.Columns.Count
       nFirstRow = objWks.Rows.Count

       For Each objAreaRange In objUsedRange.Areas
          nFirstColA = objAreaRange.Column
          nFirstRowA = objAreaRange.Row

          nLastColA = nFirstColA + objAreaRange.Columns.Count - 1
          nLastRowA = nFirstRowA + objAreaRange.Rows.Count - 1

          If nFirstColA < nFirstCol Then nFirstCol = nFirstColA
          If nFirstRowA < nFirstRow Then nFirstRow = nFirstRowA

          If nLastRowA > nLastRow Then nLastRow = nLastRowA
          If nLastColA > nLastCol Then nLastCol = nLastColA
       Next objAreaRange

       Set GetDataRange = _
            objWks.Range(objWks.Cells(nFirstRow, nFirstCol), _
            objWks.Cells(nLastRow, nLastCol))
    End If

    On Error GoTo 0
  End If
End Function
 
Erste und letzte Spalte und Zeile mit Daten ermitteln
Mit der obigen Funktion GetDataRange() kann nicht nur der in einem Tabellenblatt verwendete Bereich mit Daten ermittelt werden, sondern auch die erste und letzte verwendete Spalte und Zeile mit Daten.
 
Sub Demo_GetFirstAndLastColAndRow()
  Dim objWks        As Worksheet
  Dim objDataRange  As Range

  Dim nFirstCol     As Integer
  Dim nFirstRow     As Long
  Dim nLastCol      As Integer
  Dim nLastRow      As Long

  Dim strMsg        As String

  Set objWks = ActiveWorkbook.Worksheets(1)

  Set objDataRange = GetDataRange(objWks)

  If Not objDataRange Is Nothing Then
    nFirstCol = objDataRange.Column
    nFirstRow = objDataRange.Row
    nLastCol = objDataRange.Column + objDataRange.Columns.Count - 1
    nLastRow = objDataRange.Row + objDataRange.Rows.Count - 1

    strMsg = "Tabellenblatt-Name: " & objWks.Name & vbCr & vbCr
    strMsg = strMsg & "Erste Spalte: " & vbTab & nFirstCol & vbCr
    strMsg = strMsg & "Letzte Spalte: " & vbTab & nLastCol & vbCr
    strMsg = strMsg & "Erste Zeile: " & vbTab & nFirstRow & vbCr
    strMsg = strMsg & "Letzte Zeile: " & vbTab & nLastRow

    Set objDataRange = Nothing

  Else
    strMsg = "Das Tabellenblatt '" & objWks.Name & _
          "' enthält keine Daten!"
  End If

  MsgBox strMsg, vbInformation, "VB-fun-Demo - " & _
        "Erste und letzte verwendete Spalte/Zeile mit Daten"

  Set objWks = Nothing
End Sub
 
Erste und letzte Spalte und Zeile mit Daten ermitteln (Find-Methode)
Alternativ kann aber auch die Find-Methode verwendet werden, um die letzte Spalte und Zeile mit Daten zu ermitteln.
In diesem Zusammenhang sei jedoch darauf hingewiesen, dass ab Excel 2000 (!) bei der Verwendung der Find-Methode in Verbindung mit zusammengeführten (verbundenen) Zellen unter bestimmten Umständen der Laufzeitfehler '91' ausgelöst wird. Der Laufzeitfehler wird ab Excel 2000 (!) beispielsweise dann ausgelöst, wenn im Tabellenblatt zwar Daten enthalten sind, diese sich aber nur in zusammengeführten Zellen befinden.
Es ist also nicht zu empfehlen, die folgenden Beispiele zu verwenden, wenn im Tabellenblatt zusammengeführte Zellen enthalten sind.
Letzte  S p a l t e  mit Daten in Tabellenblatt
 
Sub FindLastColumnInWS()
  Dim objWks    As Worksheet

  Dim nLastCol  As Integer
  Dim strMsg    As String

  Set objWks = ActiveWorkbook.Worksheets(1)

  With objWks
      If Application.WorksheetFunction.CountA(.Cells) > 0 Then
          On Error Resume Next
          nLastCol = .Cells.Find(What:="*", After:=.Cells(1), _
                      LookIn:=xlFormulas, LookAt:=xlWhole, _
                      SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious).Column

          If Err.Number = 0 Then
              strMsg = "Letzte Spalte mit Daten: " & nLastCol
          Else
              strMsg = "Laufzeitfehler '" & CStr(Err.Number)
              strMsg = strMsg & "':" & vbCr & Err.Description
          End If
          On Error GoTo 0
      Else
          strMsg = "Das Tabellenblatt enthält keine Daten!"
      End If
  End With

  strMsg = "Tabellenblatt: " & objWks.Name & vbCr & vbCr & strMsg

  MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
        "Letzte Spalte mit Daten ermitteln (Find-Methode)"

  Set objWks = Nothing
End Sub
 
Letzte  Z e i l e  mit Daten in Tabellenblatt
 
Sub FindLastRowInWS()
  Dim objWks    As Worksheet

  Dim nLastRow  As Long
  Dim strMsg    As String

  Set objWks = ActiveWorkbook.Worksheets(1)

  With objWks
      If Application.WorksheetFunction.CountA(.Cells) > 0 Then
          On Error Resume Next
          nLastRow = .Cells.Find(What:="*", After:=.Cells(1), _
                LookIn:=xlFormulas, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row

          If Err.Number = 0 Then
              strMsg = "Letzte Zeile mit Daten: " & nLastRow
          Else
              strMsg = "Laufzeitfehler '" & CStr(Err.Number)
              strMsg = strMsg & "':" & vbCr & Err.Description
          End If
          On Error GoTo 0
      Else
          strMsg = "Das Tabellenblatt enthält keine Daten!"
      End If
  End With

  strMsg = "Tabellenblatt: " & objWks.Name & vbCr & vbCr & strMsg

  MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
        "Letzte Zeile mit Daten ermitteln (Find-Methode)"

  Set objWks = Nothing
End Sub
 
Letzte  Z e i l e  mit Daten in angegebener Spalte ermitteln
Die letzte verwendete Zeile in einer angegebenen Spalte ermittelt man unter anderem auch, um Daten an einen bestehenden Datenbestand anfügen zu können. Sind in der angegebenen Spalte zusammengeführte Zellen enthalten, so wird unter Umständen jedoch ein Wert zurückgegeben, der nicht geeignet ist, die nächsten "freie" Zeile zu ermitteln.
Wenn beispielsweise die Zellen A1 und B1 zusammengeführt wurden, ist der Zellbezug die Zelle A1, also die Zelle, die sich im ursprünglichen Bereich in der oberen linken Ecke befindet. Sind nur in der Zelle A1 Daten enthalten, würde bei der Ermittlung der letzten verwendeten Zeile in Spalte 2 zurückgegeben werden, dass keine Daten in der angegebenen Spalte vorhanden sind. Es ist jedoch nicht möglich, der Zelle B1 einen Wert zuzuweisen!
 
Sub GetLastRowInColumn()
  Dim objWks    As Worksheet

  Dim nCol      As Integer
  Dim nLastRow  As Long
  Dim strMsg    As String

  Set objWks = ActiveWorkbook.Worksheets(1)

  nCol = 2
  With objWks
      If Application.WorksheetFunction.CountA( _
              .Columns(nCol).EntireColumn) > 0 Then

          If Len(.Cells(.Rows.Count, nCol).Value) = 0 Then
              nLastRow = .Cells(.Rows.Count, nCol).End(xlUp).Row
          Else
              nLastRow = .Rows.Count
          End If

          strMsg = "Die letzte Zeile, die in Spalte " & nCol & _
                 " Daten enthält, ist die Zeile " & nLastRow
      Else
          strMsg = "Die Spalte " & nCol & " enthält keine Daten!"
      End If
  End With

  strMsg = "Tabellen-Name: " & objWks.Name & vbCr & vbCr & strMsg

  MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
        "Letzte  Z e i l e  mit Daten in angegebener Spalte"

  Set objWks = Nothing
End Sub
 
Letzte  S p a l t e  mit Daten in angegebener Zeile ermitteln
Die letzte verwendete Spalte in einer angegebenen Zeile ermittelt man unter anderem auch, um Daten an einen bestehenden Datenbestand anfügen zu können. Sind in der angegebenen Zeile zusammengeführte Zellen enthalten, so wird jedoch ein Wert zurückgegeben, der unter Umständen nicht geeignet ist, die nächsten "freie" Spalte zu ermitteln.
Wurden beispielsweise die Zellen A1 und B1 zusammengeführt, ist der Zellbezug die Zelle A1, also die Zelle, die sich im ursprünglichen Bereich in der oberen linken Ecke befindet. Sind nur in der Zelle A1 Daten enthalten, würde bei der Ermittlung der letzten verwendeten Spalte in Zeile 1 die Spalte 1 zurückgegeben werden. Errechnet man nun, dass die nächste "freie" Spalte die Spalte 2 ist (Zelle B1), so müsste hier eine zusätzliche Überprüfung stattfinden, da der Zelle B1 durch das Verbinden mit Zelle A1 kein Wert zugewiesen werden kann.
 
Sub GetLastColumnInRow()
  Dim objWks    As Worksheet

  Dim nRow      As Long
  Dim nLastCol  As Integer
  Dim strMsg    As String

  Set objWks = ActiveWorkbook.Worksheets(1)

  nRow = 2
  With objWks
      If Application.WorksheetFunction.CountA( _
              .Rows(nRow).EntireRow) > 0 Then

          If Len(.Cells(nRow, .Columns.Count).Value) = 0 Then
              nLastCol = .Cells(nRow, .Columns.Count) _
                    .End(xlToLeft).Column
          Else
              nLastCol = .Columns.Count
          End If

          strMsg = "Die letzte Spalte, die in Zeile " & nRow & _
                 " Daten enthält, ist die Spalte " & nLastCol
      Else
          strMsg = "Die Zeile " & nRow & " enthält keine Daten!"
      End If
  End With

  strMsg = "Tabellenblatt: " & objWks.Name & vbCr & vbCr & strMsg

  MsgBox strMsg, vbInformation, Title:="VB-fun-Demo - " & _
        "Letzte  S p a l t e  mit Daten in angegebener Zeile"

  Set objWks = Nothing
End Sub
 
Zusammengeführte/verbundene Zellen
Wie schon mehrfach angesprochen, kann es zu Problemen kommen, wenn im angegebenen Bereich zusammengeführte Zellen enthalten sind. Mit der MergeCells-Eigenschaft kann dies überprüft werden. Weitere Infos zum Thema können der Excel-VB(A)-Hilfe entnommen werden.
 
Sub Demo_MergeCells()
  Dim varMergeCells As Variant

  varMergeCells = Worksheets(1).UsedRange.MergeCells

  If varMergeCells = False Then
      MsgBox "Keine zusammengeführten Zellen im angegebenen " & _
             "Bereich enthalten!"

  ElseIf varMergeCells = True Then
      MsgBox "Alle Zellen im angegebenen Bereich zusammengeführt!"

  ElseIf IsNull(varMergeCells) Then
      MsgBox "Es sind zusammengeführte Zellen im angegebenen " & _
             "Bereich enthalten!"

  Else
      'Kann eigentlich nicht vorkommen!
  End If
End Sub
 
Hinweis
Das Download-Beispiel ist so konzipiert, dass die oben beschriebenen "Einschränkungen" sowie der Laufzeitfehler ab Excel 2000 nachvollzogen werden können.

Windows-Version
95
98/SE
ME
NT
2000
XP
Vista
Win 7
Excel-Version
95
97
2000
2002 (XP)
2003
2007
2010


Download  (38,4 kB) Downloads bisher: [ 2104 ]

Vorheriger Tipp Zum Seitenanfang Nächster Tipp

Startseite | Projekte | Tutorials | API-Referenz | VB-/VBA-Tipps | Komponenten | Bücherecke | VB/VBA-Forum | VB.Net-Forum | DirectX-Forum | Foren-Archiv | DirectX | VB.Net-Tipps | Chat | Spielplatz | Links | Suchen | Stichwortverzeichnis | Feedback | Impressum

Seite empfehlen Bug-Report
Letzte Aktualisierung: Samstag, 9. Juli 2011