How to get the column letter of the first non blank cell of a range in VBA?

Question

I need to get the column letter of the first non-blank cell of a range. This range is basically a part of a row like.

Example: Range = A2:G2 First non blank cell is on F2 cell. Need to get 'F' and store it in a String variable. What is the most efficient way to get this?

Thanks

Answer 1

Try this:

Sub columnName()
    Dim mainRange As Range, cell As Range, columnName As String
    Set mainRange = Range("A2:G2")
    'Set mainRange = Selection
    For Each cell In mainRange.Cells
        If Not IsEmpty(cell.Value) Then
            MsgBox Split(cell.Address, "$")(1)
            Exit For
        End If
    Next cell
End Sub

Answer 2

You can get that column letter with a function:

Function firstBlankCol(rg As Range) As String
Dim x
    If rg(1) = "" Then
        x = rg(1).Address
    Else
        x = rg(1).End(xlToRight).Offset(0, 1).Address
    End If
    firstBlankCol = Split(x, "$")(1)
End Function

However, it is simpler, usually, to deal with the column number, and use that for the column argument of the Cells property.

Function firstBlankCol(rg As Range) As Long
Dim x
    If rg(1) = "" Then
        x = rg(1).Column
    Else
        x = rg(1).End(xlToRight).Column + 1
    End If
    firstBlankCol = x
End Function