excel resize vba code example

Example: excel vba resize range

' Returns a resized version of initial 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")
    
    ' Sets a resized version of initial range (2 rows below, 1 column left)
    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