VBA - Determine last cell in range?
 

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!

VBA - Determine last cell in range?
 

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" 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!

VBA - Determine last cell in range?
 

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!