Excel VBA - find first empty column

Try like this for the first empty column of row 1:

finalcolumn  =  1 + .Cells(1, 1).End(xlToRight).Column

It is quite the same logic as your code, but you start from the left and you move to the right.

There are two cases, where this code will give wrong values:

• if the last column with value is the last column in Excel ("XFD"), then it will return a column, that does not exist;

• if there is no value on the first column at all, it will return the same - 16384 + 1.

Both can be checked this way, working only with Excel versions after Excel 2003:

Function GetFinalFirstColumn(wks As Worksheet) As Long

    GetFinalFirstColumn = 1 + wks.Cells(1, 1).End(xlToRight).Column
    
    'Case for empty first row
    'Case for column XFD
    If GetFinalFirstColumn = 2 ^ 14 + 1 Then
        GetFinalFirstColumn = -1
    End If
End Function

You just need to add 1 to the last column found with data to find the first empty column.

finalcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
firstemptycolumn = finalcolumn + 1

This will find the first empty column even if it embedded in other data:

Sub FirstEmptyColumn()
    Dim i As Long

    With Application.WorksheetFunction
            For i = 1 To Columns.Count
                If .CountA(Cells(1, i).EntireColumn) = 0 Then
                    MsgBox i
                    Exit Sub
                End If
            Next i
        End With
End Sub