VBA - Determine last cell in range?

[Noozer]

I have a number of rows on an Excel worksheet. I'm writing a macro to loop
through each row and process the info on that row. The number of rows can be
variable.

How do I determine how many rows are in use? When I type CTRL-END, the sheet
knows how many rows to go down. I'd like that capability in my macro.

How I loop now... Which always ends up creating 999 rows, even if I only
have 10 rows of data.

'Create an object to work with our sheet
Set sht = Excel.Sheets(1)

'Loop through all the rows from 11 to 999
For Each fromCell In sht.Range("D11999")

Thanks!

 

[Chip Pearson]

Try


For Each frmCell In Application.Intersect( _
ActiveSheet.UsedRange, Columns(4)).Cells


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Noozer,

Use sht.Cells(11,4).End(xlDown)

You can also use xlToLeft, xlToRight or xlUp if you want to navigate in
another direction.

Regards,

Chris.