Defining Range of For Each Loop for Cells and Worksheets

I often use:

For Each Cell in Worksheet.UsedRange

My understanding is that you cannot alter the UsedRange
property as it is read only. Is it possible to define a
range up front and use this in conjuction with a For Each
Loop using Cell and Worksheet. Best case scenario my goal
would be to either use this range to limit the area
covered by the For Each loop. Or worse case scenario use
it to limit the actions undertakeh within the loop

Dim NewRange as Range
For Each Cell in Worksheet.Something????
Next

Or

Dim NewRange as Range
'Note NewRange would be a subst of .UsedRange

For Each Cell in Worksheet.UsedRange
If Cell.Address is within NewRange Then
do something
Next

In the context that you show, you are reading it!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I often use:
>
> For Each Cell in Worksheet.UsedRange
>
> My understanding is that you cannot alter the UsedRange
> property as it is read only. Is it possible to define a
> range up front and use this in conjuction with a For Each
> Loop using Cell and Worksheet. Best case scenario my goal
> would be to either use this range to limit the area
> covered by the For Each loop. Or worse case scenario use
> it to limit the actions undertakeh within the loop
>
> Dim NewRange as Range
> For Each Cell in Worksheet.Something????
> Next
>
> Or
>
> Dim NewRange as Range
> 'Note NewRange would be a subst of .UsedRange
>
> For Each Cell in Worksheet.UsedRange
> If Cell.Address is within NewRange Then
> do something
> Next
>
>

if your data is like a database, perhaps

for each cell in Activesheet.Range("A1").CurrentRegion

Next cell


As a Test:
click in cell A1. Do Ctrl+Shift+8

does this select all the data you want to process? If so, that is the
current region.

--
Regards,
Tom Ogilvy

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I often use:
>
> For Each Cell in Worksheet.UsedRange
>
> My understanding is that you cannot alter the UsedRange
> property as it is read only. Is it possible to define a
> range up front and use this in conjuction with a For Each
> Loop using Cell and Worksheet. Best case scenario my goal
> would be to either use this range to limit the area
> covered by the For Each loop. Or worse case scenario use
> it to limit the actions undertakeh within the loop
>
> Dim NewRange as Range
> For Each Cell in Worksheet.Something????
> Next
>
> Or
>
> Dim NewRange as Range
> 'Note NewRange would be a subst of .UsedRange
>
> For Each Cell in Worksheet.UsedRange
> If Cell.Address is within NewRange Then
> do something
> Next
>
>