Resize a Rectangle shape according to the total number of row using VBA

**LalitPandey87** · 10-19-2011, 09:41 AM

Hi All,

Thanks for all the fast reply of my previous threads.
You people are so helpful.
Now this time i need a code or function which resize a ractangle according to the no of rows data in excel using VBA.

Thanx in Advance.

**Excel Fox** · 10-19-2011, 08:11 PM

Code:

Public Sub ResizeShapeOverRange(shp As Object, rngRange As Range)

    shp.Height = rngRange.Height
    shp.Width = rngRange.Width
    shp.Top = rngRange.Cells(1).Top
    shp.Left = rngRange.Cells(1).Left
    
End Sub

Sub Callit()

    Call ResizeShapeOverRange(ActiveSheet.Shapes(1), Range("C5:D10"))
    'OR
    Call ResizeShapeOverRange(ActiveSheet.Shapes("NameOfShape"), Range("C5:D10"))

End Sub