tip: extending a range

[Patrick Molloy]

Many people just use rows(n).Insert to extend a range
supposing you want ot extend teh range without the insert?
Here's one way :

Option Explicit
Sub test()
addRows Worksheets("sheet1").Range("myrange"), 2
End Sub
Sub addRows(target As Range, howmany As Long)
With target
With .Resize(.Rows.Count + howmany)
.Name = target.Name.Name
End With
End With
End Sub


you may want to add colr, borders etc...
I hope this may help somebody - and it may give you ideas.

Patrick

 

[Rick Rothstein]

Instead of making the user construct a range to pass into your AddRows
subroutine, why not just let them pass the Defined Name in and just process
that...

Sub AddRows(DefinedName As String, HowMany As Long)
With Names(DefinedName).RefersToRange
.Resize(.Rows.Count + HowMany).Name = .Name.Name
End With
End Sub

 

[Patrick Molloy]

good idea - thats what I'd do.

This is intended to open up ideas. so thanks very much