VBA - Range.Row.Count
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25056372/
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
VBA - Range.Row.Count
提问by docjay
I have written a simple code to illustrate my predicament.
我写了一个简单的代码来说明我的困境。
Sub test()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim k As Long
k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
End Sub
What happens is this: We count the rows which contain values starting at A1. If the number of rows which contain values is > 1 the code works great. However, if A1 is the only cell which contains any value, k = 1,048,576 which I guess is the maximum number of rows allowed in Excel.
发生的事情是这样的：我们计算包含从 A1 开始的值的行。如果包含值的行数 > 1，则代码效果很好。但是，如果 A1 是唯一包含任何值的单元格，则 k = 1,048,576，我猜这是 Excel 中允许的最大行数。
Why doesn't k = 1?
为什么 k = 1？
Pictures:
图片：
EDIT: The workaround that I'm using is the following:
编辑：我使用的解决方法如下：
Sub test()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim k As Long
k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
If k = 1048576 Then
k = 1
End If
MsgBox (k)
End Sub
Since k is always equal to 1048576 when the number of rows with values is 1. It just feels a bit silly having to do something like this.
由于当具有值的行数为 1 时，k 始终等于 1048576。不得不做这样的事情感觉有点傻。
回答by Charles Williams
Probably a better solution is work upwards from the bottom:
可能更好的解决方案是从底部向上工作：
k=sh.Range("A1048576").end(xlUp).row
回答by neelsg
You should use UsedRangeinstead like so:
你应该UsedRange像这样使用：
Sub test()
Dim sh As Worksheet
Dim rn As Range
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim k As Long
Set rn = sh.UsedRange
k = rn.Rows.Count + rn.Row - 1
End Sub
The + rn.Row - 1part is because the UsedRange only starts at the first row and column used, so if you have something in row 3 to 10, but rows 1 and 2 is empty, rn.Rows.Countwould be 8
这+ rn.Row - 1部分是因为 UsedRange 仅从使用的第一行和第一列开始，所以如果您在第 3 到 10 行中有内容，但第 1 和第 2 行为空，则为rn.Rows.Count8
回答by Dado
CountRows = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
回答by suiluj_julius
You could also use the "Last" function from Ron de Bruin (http://www.rondebruin.nl/win/s9/win005.htm), it worked perfectly for me and also gives back the last column and cell if you want. To get the last row use it like
您还可以使用 Ron de Bruin ( http://www.rondebruin.nl/win/s9/win005.htm)的“Last”函数，它对我来说非常有用，如果需要，还可以返回最后一列和单元格. 要获得最后一行，请使用它
lastRow = Last(1,yourRange)
I found this quite handy.
我发现这很方便。
Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Long
Select Case choice
Case 1:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
On Error Resume Next
lcol = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
On Error Resume Next
Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0
End Select
End Function
回答by greg
In case anyone looks at this again, you can use this:
万一有人再看这个，你可以使用这个：
Sub test()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim k As Long
k = sh.Range("A1", sh.Range("A1").End(xlDown).End(xlDown).End(xlUp)).Rows.Count
End Sub
回答by user9089345
k = sh.Range("A2", sh.Range("A1").End(xlDown)).Rows.Count
or
或者
k = sh.Range("A2", sh.Range("A1").End(xlDown)).Cells.Count
or
或者
k = sh.Range("A2", sh.Range("A1").End(xlDown)).Count
回答by lowak
That is nice question :)
这是个好问题:)
When you have situation with 1 cell (A1), it is important to identify if second declared cell is not empty (sh.Range("A1").End(xlDown)). If it is true it means your range got out of control :) Look at code below:
当您遇到 1 个单元格 (A1) 的情况时，重要的是要确定第二个声明的单元格是否不为空 ( sh.Range("A1").End(xlDown))。如果是真的，这意味着你的范围失控了 :) 看看下面的代码：
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Arkusz1")
Dim k As Long
If IsEmpty(sh.Range("A1").End(xlDown)) = True Then
k = 1
Else
k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
End If
回答by lowak
Have you tried :-
你有没有尝试过 ：-
Sub test()
k = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (k)
End Sub
The /only/ catch is that if there is no data it still returns 1.
/only/ 的问题是，如果没有数据，它仍然返回 1。
回答by pnacamuli
This works for me especially in pivots table filtering when I want the count of cells with data on a filtered column. Reduce kaccordingly (k - 1)if you have a header row for filtering:
这对我有用，特别是在数据透视表过滤中，当我想要过滤列上包含数据的单元格计数时。如果您有用于过滤的标题行，请k相应减少(k - 1)：
k = Sheets("Sheet1").Range("$A:$A").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Count
回答by APO69
The best solution is to use
最好的解决方案是使用
Cells(Rows.Count, 1).End(xlUp).Row
since it counts the number of cells until it finds the last one written.
因为它会计算单元格的数量，直到找到最后一个写入的单元格。
Unlike
不像
Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
what it does is select an "from-to" range and display the row number of the last one busy.
A range implies two minimum values, so ... meanwhile A1 has a value of the range continues to count to the limit (1048576) then it is shown.
它所做的是选择一个“from-to”范围并显示最后一个 busy 的行号。
一个范围意味着两个最小值，所以......同时 A1 有一个范围的值继续计数到极限 (1048576) 然后它被显示出来。
Sub test()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(1)
Dim k As Long
k = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox k
k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
MsgBox k
End Sub
