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("D11:D999")

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


"Noozer" <dont.spam@me.here> wrote in message
news:chKLf.66589$sa3.30172@pd7tw1no...
>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("D11:D999")
>
> Thanks!
>

[Chris Marlow]

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Noozer" wrote:

> 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("D11:D999")
>
> Thanks!
>
>
>