blocks|key|871828|text|我喜欢这样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|871829|ActiveSheet.UsedRange.Rows.Count|offset|length|style|CODE|871830|对列计数也可以做到这一点。对我来说，总是工作。但是，如果您在另一列中有数据，上面的代码也会考虑它们，因为代码将查找工作表中的所有单元格区域。|871831|entityMap^0|0|0|W|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I like this way:

<code>ActiveSheet.UsedRange.Rows.Count</code>

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.

blocks|key|871965|text|我用一张很长的测试表比较了所有的可能性：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|871966|0,140625秒|871967|lastrow+=+calcws.Cells.Find("*",+[A1],+,+,+xlByColumns,+xlPrevious).row|code-block|syntax|javascript|871968|0秒用于|871969|iLastRow+=+calcws.Cells(rows.count,+"a").End(xlUp).row|871970|和|871971|numofrows+=+calcws.Cells.SpecialCells(xlLastCell).row|871972|0,0078125秒|871973|lastrow+=+calcws.UsedRange.rows.count
Do+While+1
++++If+calcws.Cells(lastrow,+1).Value+=+""+Then
++++++++lastrow+=+lastrow+-+1
++++Else
++++++++Exit+Do
++++End+If
Loop|871974|我认为最受欢迎的是显而易见的..。|871975|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

I compared all possibilities with a long test sheet:

0,140625 sec for

<pre><code>lastrow = calcws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).row
</code></pre>

0 sec for

<pre><code>iLastRow = calcws.Cells(rows.count, "a").End(xlUp).row
</code></pre>

and

<pre><code>numofrows = calcws.Cells.SpecialCells(xlLastCell).row
</code></pre>

0,0078125 sec for

<pre><code>lastrow = calcws.UsedRange.rows.count
Do While 1
 If calcws.Cells(lastrow, 1).Value = "" Then
 lastrow = lastrow - 1
 Else
 Exit Do
 End If
Loop
</code></pre>

I think the favourites are obvious...

blocks|key|654567|text|Dim+RowNumber+As+Integer|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|654568|RowNumber+=+ActiveSheet.Range("A65536").End(xlUp).Row|654569|在您的情况下，它应该返回#9|654570|entityMap^0|0|O|0|0|1H|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|K|$]]

<code>Dim RowNumber As Integer</code> 
<code>RowNumber = ActiveSheet.Range("A65536").End(xlUp).Row</code>

In your case it should return #9

blocks|key|654611|text|在另一个网站上找到了这种方法。它适用于新的更大尺寸的Excel，不需要您硬编码最大行数和列数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|654612|iLastRow+=+Cells(Rows.Count,+"a").End(xlUp).Row
iLastCol+=+Cells(i,+Columns.Count).End(xlToLeft).Column|code-block|syntax|javascript|654613|Thanks+to+mudraker+in+Melborne,+Australia|offset|length|654614|entityMap|0|LINK|mutability|MUTABLE|url|http://www.ozgrid.com/forum/showthread.php?t=64445^0|0|0|0|15|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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.

<pre><code>iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
</code></pre>

<a href="http://www.ozgrid.com/forum/showthread.php?t=64445" rel="nofollow">Thanks to mudraker in Melborne, Australia</a>

blocks|key|654448|text|这两种方法都可以工作，让Excel定义最后一次查看数据的时间|type|unstyled|depth|inlineStyleRanges|entityRanges|data|654449|numofrows+=+destsheet.UsedRange.SpecialCells(xlLastCell).row

numofrows+=+destsheet.Cells.SpecialCells(xlLastCell).row|code-block|syntax|javascript|654450|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

These would both work as well, letting Excel define the last time it sees data 

<pre><code>numofrows = destsheet.UsedRange.SpecialCells(xlLastCell).row

numofrows = destsheet.Cells.SpecialCells(xlLastCell).row
</code></pre>

blocks|key|654763|text|++n+=+ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|654764|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> n = ThisWorkbook.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
</code></pre>

blocks|key|872033|text|我更喜欢使用CurrentRegion属性，它相当于Ctrl-*，它将当前范围扩展到包含数据的最大连续范围。从知道将包含数据的单元格或区域开始，然后展开它。UsedRange属性有时会返回很大的区域，只是因为有人在工作表的底部做了一些格式化操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|872034|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|code-block|syntax|javascript|872035|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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
</code></pre>

blocks|key|654744|text|为了更清楚起见，我想添加一个清晰的示例并运行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|654745|++++++++++++openFileDialog1.FileName+=+"Select+File";+
++++++++++++openFileDialog1.DefaultExt+=+".xls";+
++++++++++++openFileDialog1.Filter+=+"Excel+documents+(.xls)%7C*.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+:+"%2B+numRows.ToString());

++++++++++++++++xlWorkBook.Close(true,+misValue,+misValue);
++++++++++++++++xlApp.Quit();

++++++++++++}|code-block|syntax|javascript|654746|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For greater clarity, I want to add a clear example and running

<pre><code> 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();

 }
</code></pre>

column A has data like this (ie frequent blank cells):

<pre><code>HEADING &lt;-- this is A1
kfdsl
fdjgnm
fdkj

gdfkj
4353

fdjk &lt;-- this is A9
</code></pre>

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: <code>A9</code>

I have tried this but it stops at the first blank cell (ie returning <code>A4</code>)

<pre><code>numofrows = destsheet.Range("A2").End(xlDown).Row - 1
</code></pre>

How to count the number of rows in excel with data?

列A有这样的数据(即经常出现的空单元格)：HEADING <-- this is A1kfdslfdjgnmfdkjgdfkj4353fdjk <-- this is A9我希望能够获得具有数据的最后一个单元格的单元格引用。因此，在上面的示例中，我想返回：A9我试过了，但它停在第一个空单元格(即返回A4)numofrows = destsheet.Range("A2").End(xlDown)

问如何统计excel中有数据的行数？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何统计excel中有数据的行数？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何统计excel中有数据的行数？
EN