Die Auf­gabe: Eine Funk­tion (UDF) soll in ein­er bes­timmten Spalte die erste freie oder die erste leere Zeile gefun­den wer­den. Der Unter­schied zwis­chen „frei” und „leer” ist nicht auf den ersten Blick erkennbar und zugegeben­er­maßen auch willkür­lich von mir für diese Sit­u­a­tion zugewiesen wor­den. Eine Zelle ist „leer”, wenn sie tat­säch­lich leer ist, also kein­er­lei Inhalt hat. Als „frei” beze­ichne ich eine Zelle, die leer aussieht aber in irgend ein­er Form eine Funk­tion enthält, welche den Inhalt "" in die Zelle schreibt. Aus­drück­lich weise ich darauf hin, dass Zellen, welche ‑wie auch immer- als „unsicht­bar” for­matiert wur­den, wed­er „leer” sind noch als „frei” gel­ten.

Die bei­den fol­gen­den Funk­tio­nen geben jew­eils die Zeilen­num­mer der ersten freien bzw. leeren Zeile ein­er Spalte zurück. Die Spalte wird als Funk­tions-Argu­ment übergeben und kann entwed­er der numerische Wert sein (1, 2, 3, … n) oder der alphanu­merische Wert ("A", "b", "AC", …)  sein, wobei Groß- Klein­schrei­bung keine Rolle spielt.

Einige Erk­lärun­gen und Hin­weise: Bei eini­gen, weni­gen auszuw­er­tenden Zeilen reicht auch völ­lig eine Zählschleife (For .. Next) oder ein For .. Each – Kon­strukt. Das ist zwar langsam gegenüber dem fol­gen­den Code aber fällt bei ein­er über­schaubaren Zeilen­zahl nicht wirk­lich auf.

Eine weit­ere Frage stellt sich rasch: Warum nicht grund­sät­zlich in Zeile 1 posi­tion­ieren und dann Strg↓ per Code aus­führen? Wenn gar keine oder nur die erste Zeile der entsprechen­den Spalte Inhalte hat, dann kommt es zu ein­er Fehler­mel­dung.

Hier nun der Code für bei­de Funk­tio­nen. Wie Sie Funk­tio­nen ein­binden, kön­nen Sie hier nach­le­sen. Und selb­stver­ständlich dür­fen Sie den Code Ihren Bedürfnis­sen 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 ver­schiedene Szenar­ien durchge­spielt und dabei den einen oder anderen Fehler beseit­igt. Soll­ten Sie in ein­er bes­timmten Sit­u­a­tion eine Unregelmäßigkeit ent­deck­en, bitte ich Sie um die (anonymisierte) Mus­ter­datei mit­möglichst genauer Beschrei­bung der Sit­u­a­tion und des Umfeldes (Win­dows-Ver­sion, Excel-Ver­sion).

Hin­weis: Der Code wurde im März 2015 kom­plett über­ar­beit­et, weil von der ersten Funk­tion unter gewis­sen Bedin­gun­gen nicht die erste freie son­dern die let­zte gefüllte Zeile zurück gegeben wor­den ist. Bei der Gele­gen­heit wurde auch die zweite Funk­tion gründlich „aufge­frischt”. 🙂

Hin­weis: Der Code kann hier als *.txt und *.bas (zu direk­ten Import) als *.zip-Datei herunter geladen wer­den. Sie brauchen zum ent­pack­en kein Ken­nwort.