.net 如何用数据计算excel中的行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6301665/
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 to count the number of rows in excel with data?
提问by pjj
column A has data like this (ie frequent blank cells):
A列有这样的数据（即频繁的空白单元格）：
HEADING <-- this is A1
kfdsl
fdjgnm
fdkj
gdfkj
4353
fdjk <-- this is A9
I would like to be able to get the cell reference of the last cell that has data. So in the above example, I want to return: A9
我希望能够获得具有数据的最后一个单元格的单元格引用。所以在上面的例子中，我想返回：A9
I have tried this but it stops at the first blank cell (ie returning A4)
我试过这个，但它停在第一个空白单元格（即返回A4）
numofrows = destsheet.Range("A2").End(xlDown).Row - 1
回答by Tomamais
I like this way:
我喜欢这种方式：
ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Rows.Count
The same can be done with columns count. For me, always work. But, if you have data in another column, the code above will consider them too, because the code is looking for all cell range in the sheet.
列数也可以这样做。对我来说，永远工作。但是，如果您在另一列中有数据，上面的代码也会考虑它们，因为代码正在查找工作表中的所有单元格范围。
回答by nixda
Safest option is
最安全的选择是
Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Lastcol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Don't use UsedRangeor SpecialCells(xlLastCell)or End(xlUp). All these methods may give wrong results if you previously deleted some rows. Excel still counts these invisiblecells.
不要使用UsedRangeorSpecialCells(xlLastCell)或End(xlUp)。如果您之前删除了一些行，所有这些方法都可能给出错误的结果。Excel 仍然对这些不可见的单元格进行计数。
These methods will work again if you delete your cells, save the workbook, close and re-open it.
如果您删除单元格、保存工作簿、关闭并重新打开它，这些方法将再次起作用。
回答by Jean-Fran?ois Corbett
This will work, independent of Excel version (2003, 2007, 2010). The first has 65536 rows in a sheet, while the latter two have a million rows or so. Sheet1.Rows.Countreturns this number dependent on the version.
这将起作用，独立于 Excel 版本（2003、2007、2010）。第一个在一张表中有 65536 行，而后两个有大约一百万行。Sheet1.Rows.Count根据版本返回此数字。
numofrows = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
or the equivalent but shorter
或同等但更短的
numofrows = Sheet1.Cells(Sheet1.Rows.Count,1).End(xlUp)
This searches up from the bottom of column A for the first non-empty cell, and gets its row number.
这将从 A 列的底部向上搜索第一个非空单元格，并获取其行号。
This also works if you have data that go further down in other columns. So for instance, if you take your example data and also write something in cell FY4763, the above will still correctly return 9 (not 4763, which any method involving the UsedRangeproperty would incorrectly return).
如果您的数据在其他列中进一步下降，这也适用。因此，例如，如果您使用示例数据并在单元格 FY4763 中写入一些内容，则上述内容仍将正确返回 9（不是 4763，任何涉及该UsedRange属性的方法都会错误地返回）。
Note that really, if you want the cell reference, you should just use the following. You don't have to first get the row number, and then build the cell reference.
请注意，实际上，如果您想要单元格引用，您应该只使用以下内容。您不必先获取行号，然后再构建单元格引用。
Set rngLastCell = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp)
Note that this method fails in certain edge cases:
请注意，此方法在某些边缘情况下会失败：
- Last row contains data
- Last row(s) are hidden or filtered out
- 最后一行包含数据
- 最后一行被隐藏或过滤掉
So watch out if you're planning to use row 1,048,576 for these things!
因此，如果您打算将第 1,048,576 行用于这些事情，请注意！
回答by sven
I compared all possibilities with a long test sheet:
我用一张长测试表比较了所有的可能性：
0,140625 sec for
0,140625 秒
lastrow = calcws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).row
0 sec for
0 秒
iLastRow = calcws.Cells(rows.count, "a").End(xlUp).row
and
和
numofrows = calcws.Cells.SpecialCells(xlLastCell).row
0,0078125 sec for
0,0078125 秒
lastrow = calcws.UsedRange.rows.count
Do While 1
If calcws.Cells(lastrow, 1).Value = "" Then
lastrow = lastrow - 1
Else
Exit Do
End If
Loop
I think the favourites are obvious...
我认为最喜欢的是显而易见的......
回答by Lubor
Dim RowNumber As IntegerRowNumber = ActiveSheet.Range("A65536").End(xlUp).Row
Dim RowNumber As IntegerRowNumber = ActiveSheet.Range("A65536").End(xlUp).Row
In your case it should return #9
在你的情况下它应该返回 #9
回答by MonroeGA
Found this approach on another site. It works with the new larger sizes of Excel and doesn't require you to hardcode the max number of rows and columns.
在另一个站点上找到了这种方法。它适用于新的更大尺寸的 Excel，并且不需要您对最大行数和列数进行硬编码。
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
回答by Hari Seldon
These would both work as well, letting Excel define the last time it sees data
这些都可以工作，让 Excel 定义它最后一次看到数据
numofrows = destsheet.UsedRange.SpecialCells(xlLastCell).row
numofrows = destsheet.Cells.SpecialCells(xlLastCell).row
回答by Dado
n = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
回答by Andrew Magerman
I prefer using the CurrentRegion property, which is equivalent to Ctrl-*, which expands the current range to its largest continuous range with data. You start with a cell, or range, which you know will contain data, then expand it. The UsedRange Property sometimes returns huge areas, just because someone did some formatting at the bottom of the sheet.
我更喜欢使用 CurrentRegion 属性，它相当于 Ctrl-*，它将当前范围扩展到其最大的数据连续范围。您从您知道将包含数据的单元格或区域开始，然后展开它。UsedRange 属性有时会返回很大的区域，只是因为有人在工作表底部进行了一些格式化。
Dim Liste As Worksheet
Set Liste = wb.Worksheets("B Leistungen (Liste)")
Dim longlastrow As Long
longlastrow = Liste.Range(Liste.Cells(4, 1), Liste.Cells(6, 3)).CurrentRegion.Rows.Count
回答by daniele3004
For greater clarity, I want to add a clear example and running
为了更清晰，我想添加一个清晰的示例并运行
openFileDialog1.FileName = "Select File";
openFileDialog1.DefaultExt = ".xls";
openFileDialog1.Filter = "Excel documents (.xls)|*.xls";
DialogResult result = openFileDialog1.ShowDialog();
if (result==DialogResult.OK)
{
string filename = openFileDialog1.FileName;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
var numRows = xlWorkSheet.Range["A1"].Offset[xlWorkSheet.Rows.Count - 1, 0].End[Excel.XlDirection.xlUp].Row;
MessageBox.Show("Number of max row is : "+ numRows.ToString());
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
