How Do I Find The Cells With Contiguous Data In A Row In Excel VBA?

Question

Given the Image... If I know that there is some data starting at Range("B3").
How can I find the cells with contiguous data that is till cell E3? Since F3 is blank G3 onwards should not be considered. The result could either be a range object (B3:E3) or count of cells( 4 in this case).

By setting B3 as the Active cell and doing..

Range(ActiveCell, ActiveCell.End(xlToRight).Count

I do get the count, however this method is not reliable, in case only B3 has data it counts the cells till the end of the sheet.
Of course this could also be achieved by looping through the cells but I'd rather use a Worksheet Function or some other efficient method.

Answer 1

It seems that you are trying to determine the number of continuous columns used by in a row, starting from cell B3.

The code below will return the values of $B$3:$E$3 and 4 based on your data. If only cell B3 has data, it will return $B$3 and 1.

Sub GetDataArea()

Dim strCellToTest As String
Dim rngMyRange As Range
Dim lngColumns As Long

strCellToTest = "B3"

lngColumns = ActiveWorkbook.ActiveSheet.Range("" & strCellToTest).End(xlToRight).Column - 1

If lngColumns >= 256 Then
 Set rngMyRange = ActiveWorkbook.ActiveSheet.Range("" & strCellToTest)
 lngColumns = 1
Else
 Set rngMyRange = ActiveWorkbook.ActiveSheet.Range _
 (strCellToTest & ":" & Range("" & strCellToTest).Offset(0, lngColumns - 1).Address)
End If

MsgBox "Columns: " & lngColumns & vbCr & vbLf & "Range: " & rngMyRange.Address

End Sub