ActiveSheet.UsedRange.Rows.count giving wrong and different result on each run

Question

My Worksheet has 29 rows and 39 columns. Currently I use

lrow = ActiveSheet.UsedRange.Rows.count --> for getting used rows count
lColumn = ActiveSheet.UsedRange.Columns.count --> for getting used columns count

Excel gives wrong count every time it runs. Sometimes it gives:
Rows: 29 Columns: 784
On other runs it gives
Rows: 32755 and Columns as: 784
and on other runs it gives different values.

I have checked there is no junk data after 29 rows and after 39 columns. Also, Previous to filling the data I clear the sheet with: ActiveWorkbook.Worksheets("Field Difference").Cells.Delete

I hope ActiveWorkbook.Worksheets("Field Difference").Cells.Delete completely clears the sheet and clears the sheet of the junk data if any on the sheet. How else I can make sure that there is no junk data in the worksheet.

I do understand that we have other Options such as:
ActiveWorkbook.Worksheets("Field Difference").UsedRange.ClearContents - to clear only contents
ActiveWorkbook.Worksheets("Field Difference").UsedRange.Clear - to clear formatting as well.

Please do let me know why I am getting wrong values for the count of rows and columns and what is the way out. Can I use any other reliable way to get the UsedRange row count and UsedRange columns count.

Short answer: avoid using `ActiveSheet` and `UsedRange`, they're both unreliable, especially when combined... — A.S.H, Jun 21 '17 at 11:42
what is the solution then to find that UsedRange rows and columns? — sid, Jun 21 '17 at 12:17
I can't claim credit for [this answer](http://www.vbaexpress.com/kb/getarticle.php?kb_id=82), but basically `UsedRange` is not reliable under all conditions. As an example, when data has been cleared from a range, Excel still counts those cells as "used". The linked example gives a method to determine the current used range. — PeterT, Jun 21 '17 at 12:38

score 2 · Answer 1 · answered Jun 21 '17 at 12:43

2

For Last Row and Column in Column A:

Dim sht as Worksheets 
dim lRow as Long 
Set sht = Worksheets("Field Difference")

lRow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
lCol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column

answered Jun 21 '17 at 12:43

Plagon

2,689
1
11
23

If .UsedRange and/or .CurrentRegion are not providing the desired answer then typically, the data does not follow the familiar pattern of starting in A1 and radiating outwards. Granted the OP did not think it was important enough to provide a quick screenshot of the data layout but this solution will not work in a fair number of circumstances. – Jun 21 '17 at 12:49

score 1 · Answer 2 · 2017-06-21T12:52:49.347

Worksheet layout can affect the .UsedRange and .CurrentRegion properties. For a definitive 'last cell' search backwards from A1 first by rows then by columns using a blanket wildcard.

dim lr as long, lc as long

with worksheets("sheet1")
    lr = .cells.find(what:=chr(42), after:=.cells(1), searchdirection:=xlprevious, _
                     lookat:=xlpart, searchorder:=xlbyrows, lookin:=xlformulas).row
    lc = .cells.find(what:=chr(42), after:=.cells(1), searchdirection:=xlprevious, _
                     lookat:=xlpart, searchorder:=xlbycolumns, lookin:=xlformulas).column
    debug.print .cells(lr, lc).address(0, 0)

end with

A.S.H · Answer 3 · 2017-06-21T13:01:29.070

Methods for finding the last populated cell for a given column(row) are well know. using End(xlUp) (End(xlToLeft)) from the last cell of that column (row).

To get the last cell of the actually populated region of a worksheet, you can use this custom function which will get it to you reliably:

Public Function getLastCell(sh As Worksheet) As Range
  Dim lastRow As Long, lastCol As Long
  lastRow = sh.Cells.Find("*", sh.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious).Row
  lastCol = sh.Cells.Find("*", sh.Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlPrevious).Column

  Set getLastCell = sh.Cells(lastRow, lastCol)
End Function

By bringing the last cell, you can get the whole range from A1 to that cell, using

mysheet.Range("A1", getLastCell(mySheet))

Occasionally you might be interested in finding the populated region which is not starting at A1. You can, in this case, find Similarly the "Topleft" Cell of the actually populated region, using this custom function:

Public Function getFirstCell(sh As Worksheet) As Range
  Dim lastRow As Long, lastCol As Long
  lastRow = sh.Cells.Find("*", sh.Cells(sh.Rows.Count, sh.Columns.Count), xlFormulas, xlPart, xlByRows, xlNext).Row
  lastCol = sh.Cells.Find("*", sh.Cells(sh.Rows.Count, sh.Columns.Count), xlFormulas, xlPart, xlByColumns, xlNext).Column

  Set getFirstCell = sh.Cells(lastRow, lastCol)
End Function

finally, you can join the two cells to get the actually ppulated region, like this:

mysheet.Range(getFirstCell(mySheet), getLastCell(mySheet))

ActiveSheet.UsedRange.Rows.count giving wrong and different result on each run

3 Answers3

Linked