Hi,
you could use

Set myrange = ThisWorkbook.Sheets(1).UsedRange

Then
myrange.rows.count
will give you row number and
myrange.columns.count
will give you column number

Or you could use
Set myrange = ThisWorkbook.Sheets(1).Cells.SpecialCells(xlLastCe ll)

However the UsedRange property is less than perfect. For example, if you
have a spreadsheet with 10 rows of data over 10 columns then UsedRange will
show J10 correctly as the last cell. If you now clear the contents of 5
rows at the bottom and 5 columns at the right UsedRange will show E5 as the
last cell. Right? Wrong!! The property will not reset until you save the wb
or delete the cells that were used. The same is true of .SpecialCells
(xlLastCell) incredibly. So the question you are asking; ie how to
accurately find the last cell to contain data is ambiguous as how do you
define the last cell? If there is data in IV1 and data in A65536 and no
other data on a sheet, which one do you consider to be the 'last cell'?

O

--
Message posted via http://www.officekb.com