CODEWITHSUNDEEP
excelvb.netvbaexcel-2013
Fisher
Fisher

Reputation: 23

Worksheet.UsedRange.Rows.Count returns wrong result

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:
img1

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.

Upvotes: 1

Views: 12326

Answers (5)

Nooberd
Nooberd

Reputation: 1

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.

Upvotes: -1

mikebinz
mikebinz

Reputation: 406

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

Upvotes: 1

JohnyL
JohnyL

Reputation: 7162

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 img2

Upvotes: 6

Gary's Student
Gary's Student

Reputation: 96791

Sometimes it only appears too small. Say we have:

enter image description here

and we run:

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

We see:

enter image description here

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

Upvotes: 3

QHarr
QHarr

Reputation: 84465

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:

Last row

Upvotes: 1

Related Questions