Alex,
Your question is pretty general, so the following may get you
started or it may not...
'========================================
Sub CallRange()
'Displays address of revised range.
'You can replace "Selection" with the actual Range object.
MsgBox ManageRange(Selection).Address
End Sub
'========================================
'Resizes the specified range. Always uses three columns.
'Removes blank rows below the last cell with data.
Function ManageRange(ByRef objColumns As Range) As Range
Dim objRng As Range
Dim lngRows As Long
'Call function to determine last row with data.
lngRows = GetBottomRow(objColumns)
lngRows = lngRows - objColumns.Row + 1
'Resize
Set objRng = objColumns.Resize(lngRows, 3)
Set ManageRange = objRng
Set objRng = Nothing
End Function
'==========================================
' Returns the number of the last worksheet row with data.
' If the sheet is blank it returns 0.
'==========================================
Function GetBottomRow(ByRef objRange As Range) As Long
On Error GoTo NoRow
' LINE BELOW IS OPTIONAL
If objRange.Parent.FilterMode Then objRange.Parent.ShowAllData
GetBottomRow = objRange.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function
'==========================================
Regards,
Jim Cone
San Francisco, CA
