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

You could use the CurrentRegion property. This returns the range that is contiguous to the specified range. So...

Range("B3").CurrentRegion returns the range B3:E3
Range("B3").CurrentRegion.Columns.Count returns 4
Range("B3").CurrentRegion.Cells.Count also returns 4

However, if you had data in rows 4 and below (let's say you had data in B4:E6), then you would get these results

Range("B3").CurrentRegion returns the range B3:E6
Range("B3").CurrentRegion.Columns.Count returns 4
Range("B3").CurrentRegion.Cells.Count returns 16

Is this what you were after?

Answer 2

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

Answer 3

Intersect(Activecell.CurrentRegion, ActiveCell.EntireRow)

Will return B3:E3. Alternatively

If IsEmpty(ActiveCell.Offset(0,1).Value) Then
   Set rMyRange = ActiveCell
Else
   Set rMyRange = ActiveCell.Parent.Range(ActiveCell, ActiveCell.End(xlToRight))
End If

rMyRange will also return B3:E3

Answer 4

Depending on how general you need to get, it could be as simple as

Application.WorksheetFunction.Count([b4:e4])

If you want to tie in the ActiveCell, try

Application.WorksheetFunction.Count(intersect(activecell.CurrentRegion, activecell.EntireRow))

Answer 5

I like to use a function that counts columns that contain values until it encounters an empty cell. The return value can be used to set up a FOR NEXT loop to churn through a table. Here is how I would do it:

Sub tester()
    Dim Answer
    Answer = CountColumns(3, 2)
    MsgBox "There are " & Answer & " columns."
 End Sub
Public Function CountColumns(ByVal startRow As Integer, ByVal startColumn As Integer)
    'Pass starting location in spreadsheet for function to loop through until
    'empty cell is found. Return count of columns function loops through

     Do While ActiveSheet.Cells(startRow, startColumn).Value <> ""
        startColumn = startColumn + 1
     Loop
     startColumn = startColumn - 1
     CountColumns = startColumn
 End Function