Worksheet.UsedRange.Rows.Count returns wrong result

Question

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

The number of lastRow is less than used rows. I searched the site and someone suggested:

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

This returns less than actual used rows.

The solution is in the image:

I found this question it is an overall understanding of the last used row.

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.

How are you determining actual last used row? And do you have empty rows at the start? — QHarr, May 20 '18 at 11:49
yes, i have empty rows at the start and middle, last used row i mean the last row have data — Fisher, May 20 '18 at 12:51

JohnyL · Accepted Answer · 2018-05-20T12:52:06.577

6

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

edited May 20 '18 at 12:52

answered May 20 '18 at 12:14

JohnyL

6,894
3
22
41

I get it when i search the document, but i don't care if it return large than actual, i only need don't little than actual, so i used that way, but it is still incorrect, so how should i do – Fisher May 20 '18 at 12:22
@Fisher Added explanation – JohnyL May 20 '18 at 12:32
if the way you provide need the column A1's last data cell is at least equal as the last data row all the sheet. if i am clear, my english is poor – Fisher May 20 '18 at 12:42
@Fisher Added picture – JohnyL May 20 '18 at 12:51
It doesn't work when the sheet is empty. – 8oris Mar 29 '22 at 14:17

Gary's Student · Answer 2 · 2018-05-20T12:13:43.407

3

Sometimes it only appears too small. Say we have:

and we run:

Sub aRowsByAnyOtherName1()
    Dim N As Long
    N = ActiveSheet.UsedRange.Rows.Count
    MsgBox N
End Sub

We see:

The reason we get 3 rather than 4 is that the top row of the worksheet is not in UsedRange!

EDIT#1:

If the "top" of the worksheet needs to be included then use:

Sub aRowsByAnyOtherName2()
    Dim N As Long, rng As Range
    Set rng = ActiveSheet.UsedRange
    N = rng.Rows.Count + rng(1).Row - 1
    MsgBox N
End Sub

edited May 20 '18 at 12:13

answered May 20 '18 at 11:57

Gary's Student

95,722
10
59
99

wow it is exactly what i did in excel, i have some empty rows, so how can i get 4 with your case – Fisher May 20 '18 at 12:07
@Fisher See my **EDIT#1** – Gary's Student May 20 '18 at 12:14
thanks for help, can you give me the direct link please, because i open the link and don't know clearly what do you mean "EDIT#1" – Fisher May 20 '18 at 12:27
@Fisher Refresh the webpage in your browser, my **EDIT#1** will appear at the **bottom** of my Answer. – Gary's Student May 20 '18 at 12:36
i got it, i searched about the question and it's say that the UsedRange should never be used to find the lastRow or lastCol, so i guess the way you provide should be unreliable, watch this question https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba and see if it true or not – Fisher May 20 '18 at 12:48
@Fisher it **is** true! – Gary's Student May 20 '18 at 12:51
so i need to find answers continue, anyway thank you so much, i was sincere – Fisher May 20 '18 at 13:03
@Fisher Use the method outlined in Siddharth Rout's suggestion in the Post you mentioned. – Gary's Student May 20 '18 at 13:08
yeah, i am reading it and i think it is the final answer to the question, but without yours help i won't find it ,so thanks a lot – Fisher May 20 '18 at 13:11

score 1 · Answer 3 · answered May 20 '18 at 11:54

1

This is a bit of a punt on what you mean by "used" rows. If you have any blank rows at the start of the sheet you will get the wrong last row number but you will get the used range row count.

Try the following with row 1 blank.

Option Explicit

Sub test()
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    Debug.Print rng.Rows.Count                   '2
    Debug.Print ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row '3

End Sub

This yields 2 from UsedRange.Rows.Count and 3 from using xlCellTypeLastCell with data as below:

answered May 20 '18 at 11:54

QHarr

83,427
12
54
101

thanks to help, and i searched xlCellTypeLastCell after saw your answer, and i got this https://stackoverflow.com/questions/25110873/excel-application-cells-specialcellsxlcelltypelastcell-returning-bottom-of-wor it seems this way is also have some problem, i am reading it now, you can have a look – Fisher May 20 '18 at 12:24
Thank you. I will have a look! – QHarr May 20 '18 at 12:25

score 1 · Answer 4 · answered Nov 13 '18 at 18:58

I'm using Excel 2013, and as far as I can see the UsedRange and UsedRange.Count Properties work correctly

In previous versions I can remember noticing that they were unreliable, and I think this may be the reasons of some older posts, eg stackoverflow.com/questions/11169445...

Note that the UsedRange is a Single Rectangular Area bounded by the Top-, Right-, Bottom-, and Left-most Non-Blank Cells, so that the Last Used Row is thus

ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

Note also that UsedRange INCLUDES all Cells that have Any Content, for example a Border, Interior Coloring or a Comment, not just those with a Value or a Formula

Depending on what you are trying to achieve, using the SpecialCells and Find Methods may be preferable; for example the Last Used Row can also be found using

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

score -1 · Answer 5 · edited Oct 01 '21 at 00:10

UsedRange does exactly what it is supposed to do in these scenarios. How many rows of data do you have? If you have a block of data in the center of a worksheet with 3 rows and 4 columns, UsedRange returns 3 rows properly. If your worksheet has empty rows in rows 1:3, for example, you must consciously make note of that. UsedRange is very powerful if you format your workbook properly, and even MORE powerful when it's not formatted like that, because it doesn't care where the table is.

As a best practice, you should always have a single, continuous table of data on a spreadsheet starting in cell A1. If you need a summary formatted a few rows and columns in, that should be a separate sheet. And if you get a worksheet from another source that isn't formatted properly, it's your job to fix that before you analyze it -- delete empty rows and columns in front of the table.

Worksheet.UsedRange.Rows.Count returns wrong result

5 Answers5

Linked