0

Using VBA in Excel I need to find the first column that has an empty or blank cell in the first row of my spreadsheet.

I found this post that covers how to find the first ROW that has an empty or blank cell in a given column. I've tried to use this logic to look for the first empty column in a row but have not been able to do so.

This is how I've tried to adapt the logic from the post I found to get what I want but it doesn't work:

Sheets(targetWorksheet).Range("A1").End(xlToRight).Offset(1, 0).Column

I was able to put this code together which does get me the first column that has an empty or blank cell in the first row but it seems very clunky, there has to be a better way.

Dim blah As String
cellColumns = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")

For x = 0 To 3
    blah = Sheets(targetWorksheet).Range(cellColumns(x) + "1").Value
    If blah = "" Then
        MsgBox (cellColumns(x)) ' cellColumns(x) gives the column letter I'm looking for
        Exit For
    End If
Next

As always, a correct, clearly explained answer will be marked as the accepted answer and upvoted.

Thanks in advance!

braX
  • 11,506
  • 5
  • 20
  • 33
Gharbad The Weak
  • 1,541
  • 1
  • 17
  • 39
  • 1
    `Offset(1, 0)` - that's offsetting one *row*, not one column. Normally to get the first empty column you would use `.Cells(1, .Columns.Count).End(xlToLeft).Column + 1` - going from the furthermost column left. – BigBen May 13 '20 at 01:00
  • There is a difference between `xlToLeft` and `xlToRight` The former will find the first used cell looking from the anchor position "to the left". Therefore the anchor must be to the right of the expected target. Usually one specifies the last cell in a row. The latter has the anchor at the other end of the row, usually in column A, and the function looks for the first used cell that is followed by a blank, looking from left "to right". – Variatus May 13 '20 at 03:03

2 Answers2

2

First kudos to @BigBen, for identifying the standard for finding the first empty cell after all the data in Row 1.

I prefer the Find method to identify the first empty cell or column, in row 1.

You can either assign the first empty cell as a Range variable or as a Column variable, depending on how you plan to use it.

The Find method will find the first empty cell in the first row; it will select the empty cell after all cells with data if there are no blank cells in the row. If there is a blank cell before the actual last column it will select the first empty cell.

Assign the first empty cell in first row as a Range variable...

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("targetWorksheet")
Dim FirstEmptyCell As Range: Set FirstEmptyCell = ws.Cells.Find(What:="")

    FirstEmptyCell.Select

Assign the first empty cell in first row as a column variable...

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("targetWorksheet")
Dim FirstEmptyCol As Long: FirstEmptyCol = ws.Cells.Find(What:="").Column

    ws.Cells(1, FirstEmptyCol).Select
GMalc
  • 2,608
  • 1
  • 9
  • 16
1

Sheets(targetWorksheet).Range("A1").End(xlToRight).Offset(0, 1).Column

Joerg Wood
  • 141
  • 4
  • thanks for the answer, it took a little bit for me to get it to work because you didn't explain the code at all, hence upvoted but not marked as accepted answer. – Gharbad The Weak May 13 '20 at 02:48