Only step through cells that are "Active"
 

I need to write a macro that will perform an operation on every cell on the page -- but only those in th earea I've worked in. The one I've written now goes through all 65,000x65,000 cells which I most definitely DON'T want to do! This is the loop I've currently written -- how do I qualify it to stay within the active range?

(btw--the range is variable, so I can't do something like
For Each cell In Range(Cells(1, 1), Cells(20, 10)


Current loop
For Each cell In Cell
...
next cel

Thank you so much!

Only step through cells that are "Active"
 

Hi
try something like

for each cell in usedrange
'...

--
Regards
Frank Kabel
Frankfurt, Germany

"WintonCw" schrieb im Newsbeitrag
...
I need to write a macro that will perform an operation on every cell
on the page -- but only those in th earea I've worked in. The one I've
written now goes through all 65,000x65,000 cells which I most
definitely DON'T want to do! This is the loop I've currently
written -- how do I qualify it to stay within the active range?

(btw--the range is variable, so I can't do something like:
For Each cell In Range(Cells(1, 1), Cells(20, 10))
)


Current loop:
For Each cell In Cells
...
next cell


Thank you so much!

Only step through cells that are "Active"
 

Winton,

You can use the UsedRange property to get a reference to the used
portion of the worksheet. E.g.,

Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange.Cells
' do something with Rng
Next Rng



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




"WintonCw" wrote in message
...
I need to write a macro that will perform an operation on every
cell on the page -- but only those in th earea I've worked in.
The one I've written now goes through all 65,000x65,000 cells
which I most definitely DON'T want to do! This is the loop I've
currently written -- how do I qualify it to stay within the
active range?

(btw--the range is variable, so I can't do something like:
For Each cell In Range(Cells(1, 1), Cells(20, 10))
)


Current loop:
For Each cell In Cells
...
next cell


Thank you so much!

Only step through cells that are "Active"
 

How about

For Each cell In ACtiveSheet.UsedRange
'
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"WintonCw" wrote in message
...
I need to write a macro that will perform an operation on every cell on
the page -- but only those in th earea I've worked in. The one I've written
now goes through all 65,000x65,000 cells which I most definitely DON'T want
to do! This is the loop I've currently written -- how do I qualify it to
stay within the active range?

(btw--the range is variable, so I can't do something like:
For Each cell In Range(Cells(1, 1), Cells(20, 10))
)


Current loop:
For Each cell In Cells
...
next cell


Thank you so much!

Only step through cells that are "Active"
 

Winton

Sub select_range()
''good if used range has been previously reset
Dim someCells As Range
With ActiveSheet.UsedRange
Range("A1").Select
Set someCells = ActiveSheet.Range(ActiveCell, _
.Cells(.Cells.Count))
End With
someCells.Select
End Sub

The downside to the code above is that Excel has a habit of over-estimating
the actual used range.

To reset the used range before running the code, see Debra Dalgleish's site
for code.

http://www.contextures.on.ca/xlfaqApp.html#Unused

Alternative..........

Add this UDF to your workbook........

Function RangeToUse(anySheet As Worksheet) As Range
'Bob Flanagan creation slightly modified by Gord Dibben
'this function returns the range from Activecell to cell which is the
'intersection of the last row with an entry and the last column with an entry.
'used with UsedRangePick macro.....REAL USED RANGE!!
Dim i As Integer, c As Integer, R As Integer

With anySheet.UsedRange
i = .Cells(.Cells.Count).Column + 1
For c = i To 1 Step -1
If Application.CountA(anySheet.Columns(c)) 0 _
Then Exit For
Next
i = .Cells(.Cells.Count).Row + 1
For R = i To 1 Step -1
If Application.CountA(anySheet.Rows(R)) 0 Then _
Exit For
Next
End With

With anySheet
Set RangeToUse = .Range(ActiveCell, .Cells(R, c))
'note activecell could be hard-coded to a specific cell reference
End With
End Function

Then run this macro.

Sub UsedRangePick()
Dim tempRange As Range
Set tempRange = RangeToUse(ActiveSheet)
tempRange.Select
End Sub

Gord Dibben Excel MVP

On Fri, 14 May 2004 14:31:05 -0700, "WintonCw" wrote:

I need to write a macro that will perform an operation on every cell on the page -- but only those in th earea I've worked in. The one I've written now goes through all 65,000x65,000 cells which I most definitely DON'T want to do! This is the loop I've currently written -- how do I qualify it to stay within the active range?

(btw--the range is variable, so I can't do something like:
For Each cell In Range(Cells(1, 1), Cells(20, 10))
)


Current loop:
For Each cell In Cells
...
next cell


Thank you so much!