vba 如何在Excel VBA中查找连续数据的单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1592536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Do I Find The Cells With Contiguous Data In A Row In Excel VBA?
提问by Kevin Boyd
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).
鉴于图像......如果我知道有一些数据从范围（“B3”）开始。
如何找到单元格 E3 之前具有连续数据的单元格？由于 F3 是空白 G3 以后不应考虑。结果可以是范围对象 (B3:E3) 或单元格计数（在本例中为 4）。
By setting B3 as the Active cell and doing..
通过将 B3 设置为活动单元格并执行..
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.
我确实得到了计数，但是这种方法不可靠，如果只有 B3 有数据，它会计算单元格直到工作表的末尾。
当然，这也可以通过循环单元格来实现，但我宁愿使用工作表函数或其他一些有效的方法。
回答by Robert Mearns
It seems that you are trying to determine the number of continuous columns used by in a row, starting from cell B3.
您似乎正在尝试确定一行中使用的连续列数，从单元格 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.
下面的代码将根据您的数据返回 $B$3:$E$3 和 4 的值。如果只有单元格 B3 有数据，它将返回 $B$3 和 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
回答by Dick Kusleika
Intersect(Activecell.CurrentRegion, ActiveCell.EntireRow)Will return B3:E3. Alternatively
将返回 B3:E3。或者
If IsEmpty(ActiveCell.Offset(0,1).Value) Then
Set rMyRange = ActiveCell
Else
Set rMyRange = ActiveCell.Parent.Range(ActiveCell, ActiveCell.End(xlToRight))
End IfrMyRange will also return B3:E3
rMyRange 也将返回 B3:E3
回答by dendarii
You could use the CurrentRegion property. This returns the range that is contiguous to the specified range. So...
您可以使用 CurrentRegion 属性。这将返回与指定范围相邻的范围。所以...
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
但是，如果您在第 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
Is this what you were after?
这就是你追求的吗？
回答by Krashman5k
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:
我喜欢使用一个函数来计算包含值的列，直到遇到空单元格。返回值可用于设置 FOR NEXT 循环以遍历表。这是我将如何做到的：
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
回答by Ryan Shannon
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
如果要绑定ActiveCell，请尝试
Application.WorksheetFunction.Count(intersect(activecell.CurrentRegion, activecell.EntireRow))
