Excel VBA - find first empty column

Question

I know how to find last column with data:

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

But I would like to find first empty column, that is, in my case, somewhere before last column with data.

 firstemptycolumn = ?

Answer 1

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

Answer 2

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