Only step through cells that are "Active"

W

WintonCw

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

Frank Kabel

Hi
try something like

for each cell in usedrange
'...

--
Regards
Frank Kabel
Frankfurt, Germany

WintonCw said:
I need to write a macro that will perform an operation on every cell
Click to expand...
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?
 
C

Chip Pearson

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 said:
I need to write a macro that will perform an operation on every
Click to expand...
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?
 
B

Bob Phillips

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 said:
I need to write a macro that will perform an operation on every cell on
Click to expand...
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?
 
G

Gord Dibben

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
 
Top