Hi all,
I have a macro that is passed a range of any size. I want to limit processing to only the currentRegion within the passed region(otherwise processing occurs on empty cells, beyond the last used cell).
my macro is defined:
Function rangeWork(sourceRange As Range) As Integer
For i = 1 To To sourceRange.rows.Count << want to know current range row count, not the range row count
{processing here}
Next
rangeWork = total
End Function
If I pass this the entire column (eg =rangeWork(mySheet!A:C) it will process the whole col (65000 rows), where only a few rows maybe used. I realise I could test cell contents, but would prefer to constrain to the iteration to the count of rows to the currentRange within the range passed.
Any help appreciated.
Cheers & thanks
Steve
I have a macro that is passed a range of any size. I want to limit processing to only the currentRegion within the passed region(otherwise processing occurs on empty cells, beyond the last used cell).
my macro is defined:
Function rangeWork(sourceRange As Range) As Integer
For i = 1 To To sourceRange.rows.Count << want to know current range row count, not the range row count
{processing here}
Next
rangeWork = total
End Function
If I pass this the entire column (eg =rangeWork(mySheet!A:C) it will process the whole col (65000 rows), where only a few rows maybe used. I realise I could test cell contents, but would prefer to constrain to the iteration to the count of rows to the currentRange within the range passed.
Any help appreciated.
Cheers & thanks
Steve