How Do I Find The Cells With Contiguous Data In A Row In Excel VBA?
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 achiev开发者_运维知识库ed by looping through the cells but I'd rather use a Worksheet Function or some other efficient method.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
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
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?
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
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))
精彩评论