Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I disable "cutting cells" and "drag and drop "in excel ? | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
"outline active cell differently than other cells" | Excel Discussion (Misc queries) | |||
"Multiple-step operation generated errors" | Excel Programming |