How to find cells with adjacent data in a row in Excel VBA?

Question

Given the image ... If I know that there is some data starting with a range ("B3").
How can I find cells with adjacent data before cell E3? Since F3 is empty, G3 should not be considered further. The result can be either a range object (B3: E3) or the number of cells (in this case 4).

By setting B3 as the active cell and making ..

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

I get an invoice, however this method is not reliable, if only B3 has data, it counts the cells to the end of the sheet.
Of course, this can also be achieved by cycling through the cells, but I would prefer to use the worksheet function or some other efficient method.

Answer 1

It seems that you are trying to determine the number of contiguous columns used 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 2

Intersect(Activecell.CurrentRegion, ActiveCell.EntireRow)

Will return B3: E3. As an alternative

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 3

CurrentRegion. , . ...

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

, 4 (, B4: E6),

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

, ?

Answer 4

I like to use a function that counts columns containing values ​​until it encounters an empty cell. The return value can be used to set the FOR NEXT loop to retrieve the table. Here's how I 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

Answer 5

Depending on how you should share it, it might be as simple as

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

If you want to link ActiveCell, try

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