Excel VBA Find column name of first blank cell in row

Question

I am working in excel. I need to be able to find the first blank/empty cell in row 20 starting from column A. The return should be the actual column namei.e. AB, AAD, etc. What I am going to do is paste a value into this cell. Here is a picture of it with that row highlighted in green.

Dim wkb As Excel.Workbook
Dim wks2 As Excel.Worksheet
Dim strMSG As String
Dim LastRow As Long

Set wkb2 = ThisWorkbook
Set wks2 = wkb2.Sheets("Daily")
columnNumber = wks2.Cells(20, wks2.Columns.Count).End(xlToLeft).Column

I'm sorry, i don't see any picture here or a link to it. What is that you are stuck with? — Siva, Aug 17 '16 at 13:52
Possible duplicate of [Function to convert column number to letter?](http://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter) — Rémi, Aug 17 '16 at 14:04
@partyhatpanda..... deleted my answer. missed the letter part in question. :) Thanks for spotting that. — cyboashu, Aug 17 '16 at 14:05

score 1 · Accepted Answer · answered Aug 17 '16 at 14:24

Your query of: -

to find the first blank/empty cell in row

Is not answered by your code wks2.Cells(20, wks2.Columns.Count).End(xlToLeft).Column. Its a subtle but significant difference.

.End(xlToLeft) or .End(xlUp) is often used to find the last used cell in a row/column, a common requirement. To find the first used cell you either want to check each one or create a range based on all blank cells in a range, and look at the first item in that range.

The below code did it for me, and included the column reference as a letter.

Public Sub Sample()
Dim wkb2        As Excel.Workbook
Dim wks2        As Excel.Worksheet
Dim strMSG      As String
Dim StrColumn   As String
Set wkb2 = ThisWorkbook
    Set wks2 = wkb2.Worksheets("Daily")
        StrColumn = Replace(wks2.Range("20:20").SpecialCells(xlCellTypeBlanks).Cells(1, 1).Address, "$", "")
        StrColumn = Left(StrColumn, Len(StrColumn) - 2)
    Set wks2 = Nothing
Set wkb2 = Nothing
End Sub

@Jeeped I'm pretty sure there are other ways to do this, but this gets it done :) — Gary Evans, Aug 17 '16 at 14:42
My method was very close but I specified relative row and column addressing and replaced the 20 with vbNullString. — , Aug 17 '16 at 14:42

score 0 · Answer 2 · answered Aug 17 '16 at 13:57

I have a function that converts a column number to its letter, but I will try and convert it to simple code.

dim colLetter as string
Dim vArr
vArr = Split(Cells(1, columnNumber).address(True, False), "$")
colLetter = vArr(0)

I am not sure how well it works standalone. The function that I use is:

Public Function Col_Letter(lngCol As Long) As String

Dim vArr
vArr = Split(Cells(1, lngCol).address(True, False), "$")
Col_Letter = vArr(0)

End Function

And I know that works. Use whichever you like!

Excel VBA Find column name of first blank cell in row

2 Answers2