vba 数据存在的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18088729/
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
Row count where data exists
提问by Patrick Wilson
I need to count the total number of rows that have data. I want to be able to use this on multiple sheets with different amounts of data rows.
我需要计算有数据的总行数。我希望能够在具有不同数据行数量的多个工作表上使用它。
I cannot figure out generic code that will count the number of rows from A1-A100 or A1-A300.
我无法找出从 A1-A100 或 A1-A300 计算行数的通用代码。
I am trying to use something like this.
我正在尝试使用这样的东西。
i = ActiveWorkbook.Worksheets("Sheet1").Range("A2 , Range("A2").End(xlDown)).Rows.Count
回答by Joe Laviano
If you need VBA, you could do something quick like this:
如果你需要 VBA，你可以像这样快速地做一些事情：
Sub Test()
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
MsgBox lastRow
End With
End Sub
This will print the number of the last row with data in it. Obviously don't need MsgBox in there if you're using it for some other purpose, but lastRow will become that value nonetheless.
这将打印包含数据的最后一行的编号。如果您将 MsgBox 用于其他目的，显然不需要在那里，但 lastRow 将成为该值。
回答by Kazimierz Jawor
Assuming that your Sheet1is not necessary active you would need to use this improved code of yours:
假设您Sheet1不需要主动，您将需要使用您的此改进代码：
i = ActiveWorkbook.Worksheets("Sheet1").Range("A2" , Worksheets("Sheet1").Range("A2").End(xlDown)).Rows.Count
Look into full worksheet reference for second argument for Range(arg1, arg2)which important in this situation.
查看完整的工作表参考，了解Range(arg1, arg2)在这种情况下很重要的第二个参数。
回答by Markm0705
I found this method on http://www.mrexcel.com/
我在http://www.mrexcel.com/上找到了这个方法
This computes the number of non-blank cells in column A of worksheet named "Data"
这将计算名为“数据”的工作表 A 列中非空白单元格的数量
With Worksheets("Data")
Ndt =Application.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
debug.print Ndt
End With
The result is printed to the immediate window. You need to subtract 1 (or more) if column A has a header line (or lines) you do not wish to count.
结果被打印到即时窗口。如果 A 列有您不想计算的标题行（或多行），则需要减去 1（或更多）。
回答by kyoya007
lastrow = Sheet1.Range("A#").End(xlDown).Row
This is more easy to determine the row count.
Make sure you declare the right variable when it comes to larger rows.
By the way the '#' sign must be a number where you want to start the row count.
这样更容易确定行数。
确保在涉及较大的行时声明正确的变量。
顺便说一句，'#' 符号必须是您想要开始行计数的数字。
回答by Mardin Yadegar
Have you tried this?:
你试过这个吗？：
countif(rangethatyouhave, not(""))
I don't think you need to open the code editor, you can just do it in the spreadsheet itself.
我认为您不需要打开代码编辑器，您可以在电子表格本身中进行。
回答by pnacamuli
This works for me. Returns the number that Excel displays in the bottom status line when a pivotcolumnis filteredand I need the countof the visible cells.
这对我有用。返回 Excel 在底部状态行中显示的数字，当 a pivotcolumnisfiltered并且我需要count可见的cells.
Global Const DashBoardSheet = "DashBoard"
Global Const ProfileColRng = "$L:$L"
.
.
.
Sub MySub()
Dim myreccnt as long
.
.
.
myreccnt = GetFilteredPivotRowCount(DashBoardSheet, ProfileColRng)
.
.
.
End Sub
Function GetFilteredPivotRowCount(sheetname As String, cntrange As String) As long
Dim reccnt As Long
reccnt = Sheets(sheetname).Range(cntrange).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Count - 1
GetFilteredPivotRowCount = reccnt
End Function
