为什么我使用Worksheet.UsedRange.Rows.Count得到错误的结果 [英] why i use Worksheet.UsedRange.Rows.Count got wrong result

问题描述

在VB.NET中，我想获取使用的行，所以我这样写:

Dim objWorksheet As Excel.Worksheet = workbook.Sheets(2)
Dim lastRow = objWorksheet.UsedRange.Rows.Count

但是我不知道为什么lastRow比实际使用的行的数字小，我搜索了stackoverflow，有人建议这样写:

Dim range As Excel.Range = objWorkSheet.UsedRange
Dim lastRow = range.Rows.Count

我尝试了一下，也没有用，num返回的数比实际使用的行少，这是没有规则的，所以我不知道如何处理它，任何想法都是有帮助的，非常感谢

编辑1最终答案是图像:

借助答案，我搜索了 此问题 是总体了解最后使用的行并给出最终答案

编辑2总结一下

代码UsedRange.Rows.Count表示从具有数据的第一行到具有数据的最后一行，这意味着您可以在第一个非空行到最后一个非空行之间留空行，这不会影响结果，但是如果第一行为空，则结果将比实际的非空行少一，如果前两行为空，则结果将少两行，因此链接问题说永远不要使用UsedRange.Rows.Count来获取最后一行

解决方案

请尽量避免使用UsedRange.这可能会产生误导.例如，如果您填充范围A1:B5并清除列B的内容，则UsedRange.Columns.Count将返回2-，因为Excel会记住具有格式的单元格，并将其包含在UsedRange中. /p>

更新

要获得 real 的最后一列和最后一行，请使用以下代码:

lRealLastRow = _
    Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = _
    Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

更新2

In VB.NET I want to get the used rows so I wrote that:

Dim objWorksheet As Excel.Worksheet = workbook.Sheets(2)
Dim lastRow = objWorksheet.UsedRange.Rows.Count

But I don't know why the lastRow is number little than actual used rows, I searched the stackoverflow and someone suggest write like this:

Dim range As Excel.Range = objWorkSheet.UsedRange
Dim lastRow = range.Rows.Count

I tried and also didn't work, the num returns little than actual used rows is no rules, so I don't know how to deal with it, any idea is helpful, thanks very much

EDIT 1 the final answer is image:

With the help of answers i searched this question it is overall understanding the last used row and give the final answer

EDIT 2 Make a summery

The code UsedRange.Rows.Count means from the first rows that has data to the last rows that has data, which means you can have empty rows between the first non-empty rows to the last non-empty rows which not affect the result, but if the first row is empty, the result will be one less the actual non-empty row, and if the first two row is empty the result will be two less, so the link question said never use UsedRange.Rows.Count to get the last row

解决方案

Try to avoid UsedRange. It can be misleading. For instance, if you fill range A1:B5 and clear the contents of column B, then UsedRange.Columns.Count will return 2 - because Excel remembers cells with formatting and includes them into UsedRange.

UPDATE

To get real last column and row, use following code:

lRealLastRow = _
    Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = _
    Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

UPDATE 2

这篇关于为什么我使用Worksheet.UsedRange.Rows.Count得到错误的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！