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, _
SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
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
ActiveSheet.UsedRange
Ivan
