tip: extending a range
 

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

extending a range
 

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

--
Rick (MVP - Excel)


"Patrick Molloy" wrote in message
...
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

extending a range
 

good idea - thats what I'd do.

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Patrick Molloy" wrote in message
...
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