ActiveSheet.UsedRange.Rows.count giving wrong and

Question

This question already has an answer here:

• Error in finding last used cell in Excel with VBA 12 answers

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.

Answer 1

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

Answer 2

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))

Answer 3

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