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