Home > Excel VBA makroer > Find næste tomme celle

Find den næste tomme celle med Excel VBA

Når man laver makroer med Excels VBA, har man ofte brug for at kunne finde den næste tomme celle i en kolonne eller række. Det kan f.eks. være der, man skal sætte data ind i forlængelse af ældre data.

Her følger et eksempel på en funktion, der gør netop dette. Funktionen kaldes af en procedure eller en anden funktion og får i kaldet at vide, hvilken celle den skal tage udgangspunkt i.

Public Function FindNextTomme(ByVal rCell As Range) As Range
'Finder første tomme celle nedad i en kolonne.

On Error GoTo ErrorHandle

With rCell
   'Hvis udgangscellen er tom, er det den første tomme celle.
   If Len(.Formula) = 0 Then
      Set FindNextTomme = rCell
      'Hvis cellen lige under udgangscellen er tom
   ElseIf Len(.Offset(1, 0).Formula) = 0 Then
      Set FindNextTomme = .Offset(1, 0)
   Else
      'Finder cellen efter sidste celle med indhold.
      '.End(xlDown) svarer til at trykke CTRL + pil ned.
      Set FindNextTomme = .End(xlDown).Offset(1, 0)
   End If
End With

Exit Function
ErrorHandle:
MsgBox Err.Description & ", Function FindNextTomme."
End Function

Funktionen kan selvfølgelig nemt ændres, så den i stedet for at søge nedad, søger mod venstre, højre eller opad. Det er et spørgsmål om at ændre på "Offset". (1,0) er rækken under, (0,1) er kolonnen til højre, (0,-1) er kolonnen til venstre og (-1,0) er rækken ovenover. Pas på at du f.eks. ikke siger kolonnen til venstre .Offset(0,-1), hvis du er i kolonne A - det vil udløse en fejl. Ud over Offset, skal man også ændre (xlDown) til (xlToRight), (xlToLeft) eller (xlUp).

Nedenstående lille procedure er et eksempel på, hvordan man kan kalde funktionen FindNextTomme. Den angiver celle A1 som startcelle. Hvis du vil prøvekøre, så kopier nedenstående procedure ("TestTomme") og funktionen ind i et VBA-modul. Udfyld nogle celler i kolonne A startende med celle A1. Kør derefter Sub TestTomme().

Sub TestTomme()

Dim rCell As Range

'Vi kalder funktionen og siger, at den skal starte
'søgningen i celle A1.
Set rCell = FindNextTomme(Range("A1"))

'Viser en MsgBox med celleadressen. Det er her,
'du indsætter den kode, som skal bruge fundet
'af den tomme celle.
rCell.Value = "Udfyldt af makro"
MsgBox rCell.Address & " " & rCell.value

'Rangevariabler bør sættes til Nothing, når de
'er brugt. Ellers kan de bruge hukommelse.
Set rCell = Nothing

End Sub

Find næste tomme celle som offset

I samme boldgade er følgende funktion. Den returnerer ikke første tomme celle som et Range objekt, men i stedet cellens offset (afstand) fra udgangscellen.

Public Function NextEmptyRow(ByVal rCell As Range) As Integer
'Finder den første tomme celle (nedad) i et område,
'hvor udgangsområdet er en enkelt celle.
'Inputtet er rCell, og funktionen returnerer offsetværdien
'("afstanden") til den første tomme celle. Hvis udgangscellen
'selv er tom, returneres 0 (nul).

On Error GoTo ErrorHandle

If Len(rCell.Formula) = 0 Then
   NextEmptyRow = 0
ElseIf Len(rCell.Offset(1, 0).Formula) = 0 Then
   NextEmptyRow = 1
Else
   Set rCell = Range(rCell.Offset(0, 0), _ 
   rCell.Offset(0, 0).End(xlDown))
   NextEmptyRow = rCell.Rows.Count
End If

BeforeExit:
Set rCell = Nothing

Exit Function
ErrorHandle:
MsgBox Err.Description & ", Funktionen NextEmptyRow"
Resume BeforeExit
End Function

Hvis du vil prøvekøre funktionen, så kopier dén og følgende lille procedure ind i et VBA-modul og kør proceduren "TestOffset". Proceduren bruger celle A1 som udgangscelle, så skriv noget i denne og nogle flere celler.

Sub TestOffset()

Dim i As Integer

i = NextEmptyRow(Range("A1"))
MsgBox "Offset er " & i

End Sub

Relateret