excel vba range resize at top code example
Example: excel vba resize range
Function ResizeRange(ByVal pRange As Range, ByVal pRowCount, ByVal pColumnCount)
Set ResizeRange = pRange.Resize(IIf(pRowCount > 1, pRowCount, 1), _
IIf(pColumnCount > 1, pColumnCount, 1))
End Function
Sub TestIt()
Dim rInitialRange, rResizedRange As Range
Dim iRowOffset, iColOffset As Long
iRowCount = 4
iColumnCount = 5
Set rInitialRange = Range("B1:C3")
Set rResizedRange = ResizeRange(rInitialRange, iRowCount, iColumnCount)
MsgBox "Initial range: " & rInitialRange.Address(False, False) & vbCrLf _
& vbCrLf & "Row count : " & iRowCount _
& vbCrLf & "Column count : " & iColumnCount & vbCrLf _
& vbCrLf & "New range : " & rResizedRange.Address(False, False)
End Sub