Range problem

[TAM]

Can you pleae advise where I am going wrong here - basically I am trying to
add 2 extra columns to a range but having no joy....

Range("a5").Select
ActiveCell.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="DATABASE", RefersToR1C1:= _
Selection
With Range("DATABASE")
..Resize(Columns.count + 2).Name = "DATABASE"
End With

Thanks

TAM

 

[Don]

Tam

Sub test()
Range("a5").Select
ActiveCell.CurrentRegion.Select
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 2).Name =
"database"
End Sub

Don Pistulka

 

[Dave Peterson]

With Range("DATABASE")
.Resize(, .Columns.Count + 2).Name = "DATABASE"
End With

Resize() takes two parts--the first part is for the number of rows (omitted
means don't change it).

And you'd want a . (dot) in front of .columns.count+2 so that it refers back to
the previous with object (in this case: Range("database").

this would work ok, too:
With Range("DATABASE")
.Resize(.rows.count, .Columns.Count + 2).Name = "DATABASE"
End With
(resizing the number of rows to match the number of existing rows)

And you don't need to select stuff to work with it:

With Range("a5").CurrentRegion
.Resize(, .Columns.Count + 2).Name = "DATABASE"
End With

is ok, too.

 

[TAM]

Brilliant - many thanks for your help

TAM

With Range("DATABASE")
.Resize(, .Columns.Count + 2).Name = "DATABASE"
End With

Resize() takes two parts--the first part is for the number of rows (omitted
means don't change it).

And you'd want a . (dot) in front of .columns.count+2 so that it refers back to
the previous with object (in this case: Range("database").

this would work ok, too:
With Range("DATABASE")
.Resize(.rows.count, .Columns.Count + 2).Name = "DATABASE"
End With
(resizing the number of rows to match the number of existing rows)

And you don't need to select stuff to work with it:

With Range("a5").CurrentRegion
.Resize(, .Columns.Count + 2).Name = "DATABASE"
End With

is ok, too.