Find Used area of a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know that Excel allows you to create a range of the utilized area of a sheet. However, is there an easy way to find the utilized area of a range? For instance, if my area of interest is A5:B - essentially the first two columns, starting at the fifth row. There could be 1 entry in that space, or 50. (For all practical purposes, I doubt we'd ever come close to, say, 500, so the starting out range could be something like A5:B750)

But, given those bounds, is there an easy way for me to create a sub-range that is just the utilized area of that main range?
 
Dim rnga as Range, rngb as Range
Dim rng as Range
set rnga = Cells(rows.count,1).End(xlup)
set rngb = cells(rows.count,2).end(xlup)
if rnga.row > rngb.row then
set rng = Range("A5",rnga).Resize(,2)
else
set rng = Range("A5",rngb)
end if
msgbox rng.Address

--
Regards,
Tom Ogilvy

MDW said:
I know that Excel allows you to create a range of the utilized area of a
Click to expand...
sheet. However, is there an easy way to find the utilized area of a range?
For instance, if my area of interest is A5:B - essentially the first two
columns, starting at the fifth row. There could be 1 entry in that space,
or 50. (For all practical purposes, I doubt we'd ever come close to, say,
500, so the starting out range could be something like A5:B750).
But, given those bounds, is there an easy way for me to create a sub-range
Click to expand...
that is just the utilized area of that main range?
 
Probably overkill, but:

Function SubRange(BigRange As Range) As Range
Set SubRange = AntiRange(BigRange.SpecialCells(xlCellTypeBlanks), _
BigRange)
End Function

Function AntiRange(rngInner As Range, rngOuter As Range) As Range
Dim c As Range
If Not Intersect(rngInner, rngOuter) Is Nothing Then
For Each c In rngOuter.Cells
If Intersect(rngInner, c) Is Nothing Then
If AntiRange Is Nothing Then
Set AntiRange = c
Else
Set AntiRange = Union(c, AntiRange)
End If
End If
Next
End If
End Function

--

Vasant




MDW said:
I know that Excel allows you to create a range of the utilized area of a
Click to expand...
sheet. However, is there an easy way to find the utilized area of a range?
For instance, if my area of interest is A5:B - essentially the first two
columns, starting at the fifth row. There could be 1 entry in that space,
or 50. (For all practical purposes, I doubt we'd ever come close to, say,
500, so the starting out range could be something like A5:B750).
But, given those bounds, is there an easy way for me to create a sub-range
Click to expand...
that is just the utilized area of that main range?
 
Back
Top