Excel VBA: Find Next Empty Cell in Row, When only Row 1 is Populated

It is Saturday and I’m about to go out and mow my lawn but I had an Excel VBA problem I wanted to get past first.

Not sure this is the best solution, but it is the one I came up with so I’ll share it with you now before I go mow.

Problem: I have a worksheet that is an event log for other subroutines. When created, the worksheet had a header row also created with data in Row 1 across columns A thru G.

When “WriteToEventLog” function call is firing off, we’re checking for next empty row in column A and cell “A1” already has header data written to it.

The method below works when cell “A2” is the starting cell, but fails when cell “A1” is used as the starting Range.

This code fails in an infinite loop and Out of Memory Error when starting from cell “A1” and ends up selecting all the cells memory can hold from column A.

        Range("A1").Activate
        ActiveCell.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select

Here is my Saturday morning, pre mow, attempt to solve this issue with a custom Excel VBA function that correctly finds the next empty cell when row 1 contains the header row.

Please email me if you read this and it has helped you in any way.

1st Pass Code:

Sub AddItemToEndOfList()
    Worksheets("Sheet1").Activate
    'We assume that row 1 is already filled out from first process
    'Check to see if cell A2 is empty, if so then enter the next value in A2.
    'if not then go to next empty cell and enter the value.
    Range("A2").Activate
    If IsEmpty(Range("A2").Value) Then
        Range("A2").Activate
        ActiveCell.Value = "http://wwww.FinditClassifeds.com"
    Else
        Range("A1").Activate
        ActiveCell.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "http://wwww.FinditClassifeds.com"
    End If
End Sub

This can all be replaced with one line of code…

2nd Pass with Improved Code:

Range("A2").End(xlDown).End(xlUp).Offset(1, 0).Select

This works every time when we know Row 1 is already populated.