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