'*****************************************************
'The UsedRegion function below has the single optional argument
''StartCell', where StartCell is the top-left of the range you want to
'be considered. StartCell is used when you wish to be selective and
'only consider portions of the UsedRegion. In the absence of a given
'StartCell, the range A1 is taken to be the default StartCell i.e. the
'entire UsedRegion is then considered as being the default region.
'*****************************************************
Option Explicit
'<< FUNCTION TO DETERMINE THE REGION CONTAINING VALUES >>
Function UsedRegion(Optional StartCell As Range) As Range
Dim LastCol&, LastRow&, Junction As Range
'//empty sheet = error
On Error Goto NotFound
'//find the last column
LastCol = Cells.Find("*", SearchOrder:=xlByColumns, _
LookIn:=xlValues, SearchDirection:=xlPrevious).Column
'//find the last row
LastRow = Cells.Find("*", SearchOrder:=xlByRows, _
LookIn:=xlValues, SearchDirection:=xlPrevious).Row
'//set the default start cell as A1
If StartCell Is Nothing Then Set StartCell = [A1]
If StartCell.Row > LastRow Or StartCell.Column > LastCol Then
'//end if the start cell is outside the used region
MsgBox "Your start cell is outside the used region"
End
Else
'//continue if the start cell is inside the used region
Set Junction = Intersect(Columns(LastCol), Rows(LastRow))
Set UsedRegion = Range(StartCell.Address & ":" & Junction.Address)
End If
Exit Function
NotFound:
'//empty sheet
MsgBox "There are no values on this sheet", , "No Used Region"
'//message alert already given (just above)
Application.DisplayAlerts = False
End
End Function
'*****************************************************
'<< A SIMPLE EXAMPLE USAGE >>
Sub UsedRegionAddy()
'//give the used region address
MsgBox "The Used Region address is " & UsedRegion.Address
End Sub
'*****************************************************
'<< ANOTHER SIMPLE EXAMPLE USAGE >>
Sub EntireUsedRegion()
'//colour the UsedRegion from A1 to the Junction
UsedRegion.Interior.ColorIndex = 35
End Sub
'*****************************************************
'<< A LESS SIMPLE EXAMPLE USING THE OPTIONAL ARGUMENT >>
Sub TheUsedRegion()
'//more than one cell selected
If Selection.Cells.Count > 1 Then
MsgBox "Please select one cell only...", , "Ambiguous - Need One Cell"
Exit Sub
End If
'//select the area starting from selection to the junction
UsedRegion(ActiveCell).Select
If Selection.Cells.Count = 1 Then
MsgBox "(Only one cell in region)"
End If
End Sub
'*****************************************************
|