MrExcel Publishing
Your One Stop for Excel Tips & Solutions

UsedRange always giving me problems

Posted by Tim on January 17, 2002 5:23 AM

It seems to me that the UsedRange property of a worksheet sometimes returns different ranges based on conditions that I can not determine.

I especially have problems selecting Used Range and it will select rows 1-3 for example. I will clear all, delete contents, delete the rows, reinsert rows, anything...but it still thinks that those rows are used.

Again, this seems inconsistent, sometimes I don't have the problem. Any ideas or procodures that should be followed when using this property?

Posted by Dan on January 17, 2002 7:05 AM

UsedRange is quirky at best. You can use something like this to set the used range (set to variable "theRange", in this case) which works better.

Dim FirstRow, FirstCol, LastRow As Integer, LastCol As Integer
Dim theRange as Range

FirstRow = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
Set theRng = Range(Cells(FirstRow, FirstCol),Cells(LastRow, LastCol))

Posted by Ivan F Moala on January 17, 2002 8:24 PM

Yes usedrange IS quiky, I suspect it just takes
the last user input range as its last cell useage.
eg if you input data in a A65536 then delete it
it still references the last inputed range.
To over come this you can do one of 2 things
1) save the workbook
2) reset the usedrange Via