Excel2000: Getting the UsedRange

[drew4unc]

I'm having trouble determining the UsedRange of the sheet in a macro. I seem to remember doing it with no trouble a while back in 97. Anybody have a foolproof way of automatically determining the UsedRange?

Along that same vein...Once I have the UsedRange, what's the best way to get the numbers for the first/last row and first/last column? Knowing the exact range to process would certainly speed up processing times.

Thanks for the help.

Drew

 

[Damon Ostrander]

Hi Drew,

The trick with UsedRange object is that the worksheet qualifier is always required. Even for the active worksheet your must use

ActiveSheet.UsedRange

to reference the UsedRange object.

Regarding first/last row and column on a worksheet:

With ActiveSheet.UsedRange
FirstRow = .Row
LastRow = FirstRow - 1 + .Rows.Count
FirstColumn = .Column
LastColumn = FirstColumn - 1 + .Columns.Count
End With