Excel: Finding CurrentRange within a Range 2

[sdk]

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

 

[EastWind]

I'm not sure that I understand your problem exactly, but I believe you are trying to figure out how to isolate the last used row within a column. Below is how I did something similar...how to isolate the last used column within the row. Basically, to do it the other way around, you would use the Cells(Cells.SpecialCells(xlCelltypeLastCell).Row), varRow). I hope this helps.


Public Sub SelectTitleRange()
Dim FirstCell as Excel.Range, LastCell as Excel.Range
'''''''''''''''''''''''''''''''''''''''''
'Select all used cells of the title row.'
'''''''''''''''''''''''''''''''''''''''''
Set FirstCell = Range(&quot;A1&quot
Set LastCell = Cells(1, Cells.SpecialCells(xlCellTypeLastCell).Column)

Excel.Application.Range(FirstCell.Address, LastCell.Address).Select

 

[sdk]

Hi,

Thanks for that - trying too hard to explain something simple. Will give your method a go, but looks the goods.

Cheers & thanks again

 

[SkipVought]

Hi,

There are a couple posibilities.

1. the UsedRange property of a worksheet
[tt]
With ws.UsedRange
lRowFirst = .Row
lRowLast = lRowFirst + .Rows.Count - 1
End With
[/tt]
2. the CurrentRegion property of a range
[tt]
With ws.UsedRange
With .CurrentRegion
lRowFirst = .Row
lRowLast = lRowFirst + .Rows.Count - 1
Ebd With
End With
[/tt]
The latter case can produce a range that is less than the former IF there are 2 or more non-contiguous ranges within the UsedRange. Again, the latter case defines the upper-left most contiguous range within the UsedRange.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[EastWind]

Thanks SkipVought! The way I did it is far more convoluted than it needed to be. I don't use Excel VBA commands enough to really know what is out there...yet...just enough to quick-and-dirty working code.

You certainly deserve the star!

The EastWind