tip: extending a range

  • Thread starter Thread starter Patrick Molloy
  • Start date Start date
P

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
 
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
 
good idea - thats what I'd do.

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