Allowing Column Ranges in UDF

  • Thread starter Thread starter Nirmal Singh
  • Start date Start date
N

Nirmal Singh

I have a User Defined Function which takes a Range as an argument.

It processes the range as follows

For RowNumber = 1 To DateRange.Rows.Count
DateFrom = DateRange.Cells(RowNumber, 1).Value
DateTo = DateRange.Cells(RowNumber, 2).Value
......(Further Processing)
Next RowNumber

This works fine for a range such as "C23:D50".

How can I cater for the user entering a column range such as "C:D"? In this
case I would want to loop down column C while it had valid data.

Nirmal
 
I'm not sure what valid data is, but maybe you could just use the usedrange:

function myfunc(rng as range) as Variant

dim myRng as range
with rng
set myrng = nothing
on error resume next
'used range and just the first area???
set myrng = intersect(.parent.usedrange,.areas(1))
on error goto 0
end with

if myrng is nothing then
myfunc = "invalid range"
exit function
end if

'just two columns?
set myrng = myrng.columns(1).resize(,2)

'and now work against myrng

....
End function
 
Back
Top