Excel VBA: How to Extend a Range Given a Current Selection
Here is the simple code that I use to resize an existing selection.
Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count + 50).Select
This will add 5 to the row count and 50 to the column count. Adapt to suit your needs.
You mean like this?
SYNTAX
ExpandRange [Range], [Number of Col on left], [Number of Rows on Top], [Number of Col on right], [Number of Rows down]
Sub Sample()
Debug.Print ExpandRange(Range("B5"), 1, 1, 1, 1) '<~~ $A$4:$C$6
Debug.Print ExpandRange(Range("A1"), 1, 1, 1, 1) '<~~ Error
Debug.Print ExpandRange(Range("XFD4"), 1, 1, 1, 1) '<~~ Error
Debug.Print ExpandRange(Range("XFD1048576"), 1, 1, 1, 1) '<~~ Error
Debug.Print ExpandRange(Range("E5"), 1, 1, 1, 1) '<~~ $D$4:$F$6
End Sub
Function ExpandRange(rng As Range, lft As Long, tp As Long, _
rt As Long, dwn As Long) As String
If rng.Column - lft < 1 Or _
rng.Row - tp < 1 Or _
rng.Column + rt > ActiveSheet.Columns.Count Or _
rng.Row + dwn > ActiveSheet.Rows.Count Then
ExpandRange = "Error"
Exit Function
End If
ExpandRange = Range(rng.Offset(-1 * tp, -1 * lft).Address & ":" & _
rng.Offset(dwn, rt).Address).Address
End Function
You can use Application.WorksheetFunction.Offset()
which is richer than VBA's Offset and does everything required by the question.
I think it does what Siddharth Rout ExpandRange does, without the need of a UDF.
Range(Cells(WorksheetFunction.Max(1, Selection.Row - 1), _
WorksheetFunction.Max(1, Selection.Column - 1)), _
Cells(WorksheetFunction.Min(Selection.Worksheet.Rows.Count, _
Selection.Row + 1), _
WorksheetFunction.Min(Selection.Worksheet.Columns.Count, _
Selection.Column + 1))).Select
upd: thanks Siddharth Rout for formating my msg