excelvbacellnonblank

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

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

Solution

  • 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