Smallest range on sheet containing non-blank cells?

[Ken Johnson]

Does anyone know of a quick VBA way of determining the smallest range
on the active sheet that contains all of the cells with data.
What I mean is:
Say the active sheet has data in only B4, F8 and H6; then the smallest
range containing these cells is B4:H8 and that is the range that I then
want to work with using the SpecialCells method.
I'm hoping I can avoid looping through an array of all the sheet's cell
values.
Any ideas or suggestions?
Ken Johnson

 

[Guest]

Hi Ken,

Have you try small function? Goto excel help search and type "small"

SMALL(array,k)

Hope this helps.

 

[Nick Hebb]

Worksheet.UsedRange.Address will give you the smallest used range of
cells.

SMALL(array,k) will give you the kth smallest value in an array of
values.

HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

 

[Norman Jones]

Hi Ken,

Try something like:

'==================>>
Public Sub Tester01()
Dim rng As Range
Dim RngA As Range, RngB As Range
Dim RngBig As Range
Dim ar As Range
Dim WB As Workbook
Dim Sh As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<========== CHANGE
Set Sh = WB.Sheets("Sheet1") '<<========== CHANGE
Set rng = Sh.UsedRange
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

On Error Resume Next
Set RngA = rng.SpecialCells(xlCellTypeConstants)
Set RngB = rng.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not RngA Is Nothing Then Set RngBig = RngA

If Not RngB Is Nothing Then
If Not RngBig Is Nothing Then
Set RngBig = Union(RngB, RngBig)
Else
Set RngBig = RngB
End If
End If

If Not RngBig Is Nothing Then
For Each ar In RngBig.Areas
'do something, i.e.:
ar.Copy Destination:= _
WB.Sheets("Sheet4").Range(ar.Address)
ar.Interior.ColorIndex = 6
Next ar
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub

'<<==================

 

[Ken Johnson]

Mel, Nick and Norman,
Thanks for your help.
You've given me heaps to experiment with!
Ken Johnson