Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
I wrote a function where, amongst other things, a range is passed through. I
want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
What would the call look like if there were multiple sheets and/or multiple
columns ? If you had different numbers of cells/rows in the columns, what would you want to do ? What happens if you don't pass it a sheet name at all ? What happens if you only pass it Sheet1!D ? And how do you cope if a defined range is passed like D1:D256? Are you controlling what gets passed across or would someone else be using it? Regards Trevor "spyd3r" wrote in message ... I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
Hi,
try the UsedRange property of the worksheet object Dim r as range, cell as range set r= range("A1:C56") 'limit r to the usedRange and not beyond set r= application.intersect(r, r.parent.usedRange) debug.print r.address Now loop through each cell in r for each cell in r.cells ... next -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
Good questions Trevor, ones I wasn't sure I knew the answer to ;)
Your intersect idea works great sebastienm. It now takes 10 seconds to update the formulas in my spreasheet versus the 10 minutes it was taking before. It isn't perfect, as the .usedrange property isn't perfect, but the worst that should happen is it ends up cycling through a few extra blank rows. It shouldn't accidentally cut out any rows, at least from what I can tell? Thanks for the help! "sebastienm" wrote: Hi, try the UsedRange property of the worksheet object Dim r as range, cell as range set r= range("A1:C56") 'limit r to the usedRange and not beyond set r= application.intersect(r, r.parent.usedRange) debug.print r.address Now loop through each cell in r for each cell in r.cells ... next -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
Actually, an even better way by skipping the blank cells:
Dim rg as range, rg1 as range, rg2 as range Set rg=..... 'get cells having constants and formulas Set rg1=rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) set rg2=rg.SpecialCells(xlCellTypevalues, xlErrors + xlLogical + xlNumbers + xlTextValues) if rg1 is nothing then set rg=rg2 elseif rg2 is nothing then set rg=rg1 else set rg= application.union(rg1,rg2) endif if not rg is nothing for each cells in rg.cells ... next end if -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: Good questions Trevor, ones I wasn't sure I knew the answer to ;) Your intersect idea works great sebastienm. It now takes 10 seconds to update the formulas in my spreasheet versus the 10 minutes it was taking before. It isn't perfect, as the .usedrange property isn't perfect, but the worst that should happen is it ends up cycling through a few extra blank rows. It shouldn't accidentally cut out any rows, at least from what I can tell? Thanks for the help! "sebastienm" wrote: Hi, try the UsedRange property of the worksheet object Dim r as range, cell as range set r= range("A1:C56") 'limit r to the usedRange and not beyond set r= application.intersect(r, r.parent.usedRange) debug.print r.address Now loop through each cell in r for each cell in r.cells ... next -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
That does seem like a better way, but it doesn't seem to be working properly.
I turned it into this function (added the on error goto next, as it bombs out if it finds no cells of that type): Public Function GetUsedRange(ByVal rg As Range) As Range Dim rg1 As Range Dim rg2 As Range On Error Resume Next 'get cells having constants and formulas Set rg1 = rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) Set rg2 = rg.SpecialCells(xlCellTypeConstants, xlErrors + xlLogical + xlNumbers + xlTextValues) If rg1 Is Nothing Then Set GetUsedRange = rg2 ElseIf rg2 Is Nothing Then Set GetUsedRange = rg1 Else Set GetUsedRange = Application.Union(rg1, rg2) End If On Error GoTo 0 Set rg1 = Nothing Set rg2 = Nothing End Function If I use the following test, I get "$F$1:$F$36,$F$38:$F$82,$F$84:$F$139" as the address, which is correct: Set rng1 = Range("III!$F:$F") Set rng = GetUsedRange(rng1) Debug.Print rng.Address But, when I try to pass the range that was passed through the Excel formula, it just always returns "$F:$F". It sees formulas and constants in every cell in the given range...any idea why? All that is being passed through the Excel formula is "=calc(III!$F:$F,"avg","$",TRUE,$A2,1)", so I'm not sure how it could be different... "sebastienm" wrote: Actually, an even better way by skipping the blank cells: Dim rg as range, rg1 as range, rg2 as range Set rg=..... 'get cells having constants and formulas Set rg1=rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) set rg2=rg.SpecialCells(xlCellTypevalues, xlErrors + xlLogical + xlNumbers + xlTextValues) if rg1 is nothing then set rg=rg2 elseif rg2 is nothing then set rg=rg1 else set rg= application.union(rg1,rg2) endif if not rg is nothing for each cells in rg.cells ... next end if -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: Good questions Trevor, ones I wasn't sure I knew the answer to ;) Your intersect idea works great sebastienm. It now takes 10 seconds to update the formulas in my spreasheet versus the 10 minutes it was taking before. It isn't perfect, as the .usedrange property isn't perfect, but the worst that should happen is it ends up cycling through a few extra blank rows. It shouldn't accidentally cut out any rows, at least from what I can tell? Thanks for the help! "sebastienm" wrote: Hi, try the UsedRange property of the worksheet object Dim r as range, cell as range set r= range("A1:C56") 'limit r to the usedRange and not beyond set r= application.intersect(r, r.parent.usedRange) debug.print r.address Now loop through each cell in r for each cell in r.cells ... next -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
It would seem that no matter how I try to call the GetUsedRange function, it
won't work when being called from another function. But, it works when being called from a Sub...It doesn't work when being called directly from a spreadsheet, either (stepping through it to see). "spyd3r" wrote: That does seem like a better way, but it doesn't seem to be working properly. I turned it into this function (added the on error goto next, as it bombs out if it finds no cells of that type): Public Function GetUsedRange(ByVal rg As Range) As Range Dim rg1 As Range Dim rg2 As Range On Error Resume Next 'get cells having constants and formulas Set rg1 = rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) Set rg2 = rg.SpecialCells(xlCellTypeConstants, xlErrors + xlLogical + xlNumbers + xlTextValues) If rg1 Is Nothing Then Set GetUsedRange = rg2 ElseIf rg2 Is Nothing Then Set GetUsedRange = rg1 Else Set GetUsedRange = Application.Union(rg1, rg2) End If On Error GoTo 0 Set rg1 = Nothing Set rg2 = Nothing End Function If I use the following test, I get "$F$1:$F$36,$F$38:$F$82,$F$84:$F$139" as the address, which is correct: Set rng1 = Range("III!$F:$F") Set rng = GetUsedRange(rng1) Debug.Print rng.Address But, when I try to pass the range that was passed through the Excel formula, it just always returns "$F:$F". It sees formulas and constants in every cell in the given range...any idea why? All that is being passed through the Excel formula is "=calc(III!$F:$F,"avg","$",TRUE,$A2,1)", so I'm not sure how it could be different... "sebastienm" wrote: Actually, an even better way by skipping the blank cells: Dim rg as range, rg1 as range, rg2 as range Set rg=..... 'get cells having constants and formulas Set rg1=rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) set rg2=rg.SpecialCells(xlCellTypevalues, xlErrors + xlLogical + xlNumbers + xlTextValues) if rg1 is nothing then set rg=rg2 elseif rg2 is nothing then set rg=rg1 else set rg= application.union(rg1,rg2) endif if not rg is nothing for each cells in rg.cells ... next end if -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: Good questions Trevor, ones I wasn't sure I knew the answer to ;) Your intersect idea works great sebastienm. It now takes 10 seconds to update the formulas in my spreasheet versus the 10 minutes it was taking before. It isn't perfect, as the .usedrange property isn't perfect, but the worst that should happen is it ends up cycling through a few extra blank rows. It shouldn't accidentally cut out any rows, at least from what I can tell? Thanks for the help! "sebastienm" wrote: Hi, try the UsedRange property of the worksheet object Dim r as range, cell as range set r= range("A1:C56") 'limit r to the usedRange and not beyond set r= application.intersect(r, r.parent.usedRange) debug.print r.address Now loop through each cell in r for each cell in r.cells ... next -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
i see, yo are trying to use the function as a Worksheet Function. In this
case, you cannot use SpecialCells. With worksheet functions, some methods/properties cannot be used: Find, SpecialCells, CurrentRegion, CurrentArray... i am not sure of UsedRange actually. -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: It would seem that no matter how I try to call the GetUsedRange function, it won't work when being called from another function. But, it works when being called from a Sub...It doesn't work when being called directly from a spreadsheet, either (stepping through it to see). "spyd3r" wrote: That does seem like a better way, but it doesn't seem to be working properly. I turned it into this function (added the on error goto next, as it bombs out if it finds no cells of that type): Public Function GetUsedRange(ByVal rg As Range) As Range Dim rg1 As Range Dim rg2 As Range On Error Resume Next 'get cells having constants and formulas Set rg1 = rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) Set rg2 = rg.SpecialCells(xlCellTypeConstants, xlErrors + xlLogical + xlNumbers + xlTextValues) If rg1 Is Nothing Then Set GetUsedRange = rg2 ElseIf rg2 Is Nothing Then Set GetUsedRange = rg1 Else Set GetUsedRange = Application.Union(rg1, rg2) End If On Error GoTo 0 Set rg1 = Nothing Set rg2 = Nothing End Function If I use the following test, I get "$F$1:$F$36,$F$38:$F$82,$F$84:$F$139" as the address, which is correct: Set rng1 = Range("III!$F:$F") Set rng = GetUsedRange(rng1) Debug.Print rng.Address But, when I try to pass the range that was passed through the Excel formula, it just always returns "$F:$F". It sees formulas and constants in every cell in the given range...any idea why? All that is being passed through the Excel formula is "=calc(III!$F:$F,"avg","$",TRUE,$A2,1)", so I'm not sure how it could be different... "sebastienm" wrote: Actually, an even better way by skipping the blank cells: Dim rg as range, rg1 as range, rg2 as range Set rg=..... 'get cells having constants and formulas Set rg1=rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) set rg2=rg.SpecialCells(xlCellTypevalues, xlErrors + xlLogical + xlNumbers + xlTextValues) if rg1 is nothing then set rg=rg2 elseif rg2 is nothing then set rg=rg1 else set rg= application.union(rg1,rg2) endif if not rg is nothing for each cells in rg.cells ... next end if -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: Good questions Trevor, ones I wasn't sure I knew the answer to ;) Your intersect idea works great sebastienm. It now takes 10 seconds to update the formulas in my spreasheet versus the 10 minutes it was taking before. It isn't perfect, as the .usedrange property isn't perfect, but the worst that should happen is it ends up cycling through a few extra blank rows. It shouldn't accidentally cut out any rows, at least from what I can tell? Thanks for the help! "sebastienm" wrote: Hi, try the UsedRange property of the worksheet object Dim r as range, cell as range set r= range("A1:C56") 'limit r to the usedRange and not beyond set r= application.intersect(r, r.parent.usedRange) debug.print r.address Now loop through each cell in r for each cell in r.cells ... next -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify range variable passed through Function
That's sort of what I figured, but since SpecialCells doesn't actually do any
modification, I would think they'd allow it. Oh well. UsedRange works just fine, and gets the job done. I'll save the other function for other Subs, as it seems to work pretty well. Thanks again for all your help. "sebastienm" wrote: i see, yo are trying to use the function as a Worksheet Function. In this case, you cannot use SpecialCells. With worksheet functions, some methods/properties cannot be used: Find, SpecialCells, CurrentRegion, CurrentArray... i am not sure of UsedRange actually. -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: It would seem that no matter how I try to call the GetUsedRange function, it won't work when being called from another function. But, it works when being called from a Sub...It doesn't work when being called directly from a spreadsheet, either (stepping through it to see). "spyd3r" wrote: That does seem like a better way, but it doesn't seem to be working properly. I turned it into this function (added the on error goto next, as it bombs out if it finds no cells of that type): Public Function GetUsedRange(ByVal rg As Range) As Range Dim rg1 As Range Dim rg2 As Range On Error Resume Next 'get cells having constants and formulas Set rg1 = rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) Set rg2 = rg.SpecialCells(xlCellTypeConstants, xlErrors + xlLogical + xlNumbers + xlTextValues) If rg1 Is Nothing Then Set GetUsedRange = rg2 ElseIf rg2 Is Nothing Then Set GetUsedRange = rg1 Else Set GetUsedRange = Application.Union(rg1, rg2) End If On Error GoTo 0 Set rg1 = Nothing Set rg2 = Nothing End Function If I use the following test, I get "$F$1:$F$36,$F$38:$F$82,$F$84:$F$139" as the address, which is correct: Set rng1 = Range("III!$F:$F") Set rng = GetUsedRange(rng1) Debug.Print rng.Address But, when I try to pass the range that was passed through the Excel formula, it just always returns "$F:$F". It sees formulas and constants in every cell in the given range...any idea why? All that is being passed through the Excel formula is "=calc(III!$F:$F,"avg","$",TRUE,$A2,1)", so I'm not sure how it could be different... "sebastienm" wrote: Actually, an even better way by skipping the blank cells: Dim rg as range, rg1 as range, rg2 as range Set rg=..... 'get cells having constants and formulas Set rg1=rg.SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers + xlTextValues) set rg2=rg.SpecialCells(xlCellTypevalues, xlErrors + xlLogical + xlNumbers + xlTextValues) if rg1 is nothing then set rg=rg2 elseif rg2 is nothing then set rg=rg1 else set rg= application.union(rg1,rg2) endif if not rg is nothing for each cells in rg.cells ... next end if -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: Good questions Trevor, ones I wasn't sure I knew the answer to ;) Your intersect idea works great sebastienm. It now takes 10 seconds to update the formulas in my spreasheet versus the 10 minutes it was taking before. It isn't perfect, as the .usedrange property isn't perfect, but the worst that should happen is it ends up cycling through a few extra blank rows. It shouldn't accidentally cut out any rows, at least from what I can tell? Thanks for the help! "sebastienm" wrote: Hi, try the UsedRange property of the worksheet object Dim r as range, cell as range set r= range("A1:C56") 'limit r to the usedRange and not beyond set r= application.intersect(r, r.parent.usedRange) debug.print r.address Now loop through each cell in r for each cell in r.cells ... next -- Regards, Sébastien <http://www.ondemandanalysis.com "spyd3r" wrote: I wrote a function where, amongst other things, a range is passed through. I want to allow a range to be passed so that the multiple columns can be passed, if necessary, and on different sheets. Otherwise, I could just make the required pass through a column number...but, I want to keep the flexibility of a passed range. Once I have this range passed, I loop through each cell in the range, performing various calculations. Now, if someone passes the range "D:D", my code loops through all 65000+ cells, skipping most of the For... loop if the cell.value = "". What I would like to do is figure out the last valid cell in the passed range, before going through the For... loop, so that I can cut down the number of looped cells significantly. I could do something like this, but it seems very messy and only handles one column. I guess I'm looking for a way to speed up looping through a passed range, without looping through the entire range. Thanks for any help! Public Sub test() Dim rng As Range Dim rgn2 As Range Dim str1 As String Dim str2 As String Set rng = Range("Sheet1!D:D") str1 = rng.Worksheet.name & "!" str2 = rng.Address str1 = str1 & Left(str2, InStr(1, str2, ":") - 1) & "1:" str1 = str1 & Range(rng.Worksheet.name & "!" & Mid(str2, InStr(1, str2, ":") + 1) & "65536").End(xlUp).Address Debug.Print str1 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable passed to database query | Excel Discussion (Misc queries) | |||
Excel2000: Reading values from range, passed to function as parameter using an expression | Excel Programming | |||
ClearContents method on a passed range | New Users to Excel | |||
PrintOut macro from ?passed range.addrsess | Excel Programming | |||
Translate range name passed as string to a custom function to range addresses! | Excel Programming |