Die Aufgabe: Eine Funktion (UDF) soll in einer bestimmten Spalte die erste freie oder die erste leere Zeile gefunden werden. Der Unterschied zwischen “frei” und “leer” ist nicht auf den ersten Blick erkennbar und zugegebenermaßen auch willkürlich von mir für diese Situation zugewiesen worden. Eine Zelle ist “leer”, wenn sie tatsächlich leer ist, also keinerlei Inhalt hat. Als “frei” bezeichne ich eine Zelle, die leer aussieht aber in irgend einer Form eine Funktion enthält, welche den Inhalt "" in die Zelle schreibt. Ausdrücklich weise ich darauf hin, dass Zellen, welche -wie auch immer- als “unsichtbar” formatiert wurden, weder “leer” sind noch als “frei” gelten.

Die beiden folgenden Funktionen geben jeweils die Zeilennummer der ersten freien bzw. leeren Zeile einer Spalte zurück. Die Spalte wird als Funktions-Argument übergeben und kann entweder der numerische Wert sein (1, 2, 3, … n) oder der alphanumerische Wert ("A", "b", "AC", …)  sein, wobei Groß- Kleinschreibung keine Rolle spielt.

Einige Erklärungen und Hinweise: Bei einigen, wenigen auszuwertenden Zeilen reicht auch völlig eine Zählschleife (For .. Next) oder ein For .. Each – Konstrukt. Das ist zwar langsam gegenüber dem folgenden Code aber fällt bei einer überschaubaren Zeilenzahl nicht wirklich auf.

Eine weitere Frage stellt sich rasch: Warum nicht grundsätzlich in Zeile 1 positionieren und dann Strg↓ per Code ausführen? Wenn gar keine oder nur die erste Zeile der entsprechenden Spalte Inhalte hat, dann kommt es zu einer Fehlermeldung.

Hier nun der Code für beide Funktionen. Wie Sie Funktionen einbinden, können Sie hier nachlesen. Und selbstverständlich dürfen Sie den Code Ihren Bedürfnissen anpassen.

Option Explicit

Function ErsteFreieZeile(Sp As Variant) As Long
' Zelle mit Inhalt "" zählt als leer
   Dim rngData As Range, Zelle1 As Range, c As Range
   Dim lRow As Long, AnzLeer As Long
   Dim Rc As Long
   
   On Error GoTo ErrorHandler
   If VarType(Sp) = vbString Then Sp = Columns(Sp).Column
   With ActiveSheet
      Set Zelle1 = .Cells(1, Sp)
      lRow = .Cells(Rows.Count, Sp).End(xlUp).Row
      Set rngData = .Range(Zelle1, .Cells(lRow, Sp))
      AnzLeer = rngData.Rows.Count - WorksheetFunction.Count(rngData)
      
      Do
         'Zeile 1 ist leer aber nicht ""
         If VarType(Zelle1) = 0 Or Len(Zelle1) = 0 Then
            Rc = 1
            Exit Do
         End If
         
         Set c = Zelle1
         If lRow > 1 Then
            Do
               Set c = c.End(xlDown)
               If VarType(c) = 0 Or Len(c) = 0 Then
                  Rc = c.Row
                  Exit Do
               End If
            Loop
         Else
            Rc = 1
            
         End If
         Exit Do
      Loop
   End With
      
ErrorHandler:
   If Err.Number <> 0 Then
      MsgBox "Fehler Nr. " & Err.Number & vbCrLf & Err.Description
      Rc = 1 'Um einen undefinierten Zustand zu vermeiden
   End If
   ErsteFreieZeile = Rc
End Function

'-----------------------------------------------------------------

Function ErsteLeereZeile(Sp As Variant) As Long
' Zelle mit Inhalt "" zählt NICHT als leer
   Dim rngData As Range, Zelle1 As Range, c As Range
   Dim lRow As Long, AnzLeer As Long
   Dim Rc As Long
   
   On Error GoTo ErrorHandler
   If VarType(Sp) = vbString Then Sp = Columns(Sp).Column
   With ActiveSheet
      Set Zelle1 = .Cells(1, Sp)
      lRow = .Cells(Rows.Count, Sp).End(xlUp).Row
      Set rngData = .Range(Zelle1, .Cells(lRow, Sp))
      AnzLeer = rngData.Rows.Count - WorksheetFunction.Count(rngData)
      
      Do
         'Zeile 1 ist leer aber nicht ""
         If VarType(Zelle1) = 0 Then
            Rc = 1
            Exit Do
         End If
         
         Set c = Zelle1
         If lRow > 1 Then
            Do
               Set c = c.End(xlDown)
               If VarType(c.Offset(1, 0)) = 0 Then
                  Rc = c.Row + 1
                  Exit Do
               End If
            Loop
         Else  'lRow = 1
            Rc = 2
            
         End If
         Exit Do
      Loop
   End With
      
ErrorHandler:
   If Err.Number <> 0 Then
      MsgBox "Fehler Nr. " & Err.Number & vbCrLf & Err.Description
      Rc = 1 'Um einen undefinierten Zustand zu vermeiden
   End If
   ErsteLeereZeile = Rc
End Function

Ich habe verschiedene Szenarien durchgespielt und dabei den einen oder anderen Fehler beseitigt. Sollten Sie in einer bestimmten Situation eine Unregelmäßigkeit entdecken, bitte ich Sie um die (anonymisierte) Musterdatei mitmöglichst genauer Beschreibung der Situation und des Umfeldes (Windows-Version, Excel-Version).

Hinweis: Der Code wurde im März 2015 komplett überarbeitet, weil von der ersten Funktion unter gewissen Bedingungen nicht die erste freie sondern die letzte gefüllte Zeile zurück gegeben worden ist. Bei der Gelegenheit wurde auch die zweite Funktion gründlich “aufgefrischt”. 🙂

Hinweis: Der Code kann hier als *.txt und *.bas (zu direkten Import) als *.zip-Datei herunter geladen werden. Sie brauchen zum entpacken kein Kennwort.