I have sheets in a workbook that have rows added each month. These
sheets have named ranges but, once the new data is added I need to
expand the name ranges to include the new rows. I have been
unsuccesful in writing a macro that can adjust the name ranges to
include the new rows. The ranges are always row 1 through the last row
in a specified column.

Can anyone help?

I'm still fiddling with this and thought I had the answer. The range
should just be the data in a particular column. When I use the
following macro, the range ends up being ALL of the data on the sheet.
Any ideas what I am doing wrong?

Sheets("Sheet2").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("rangea").CurrentRegion.Name = "rangea"


Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("rangeb").CurrentRegion.Name = "rangeb"

Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("rangec").CurrentRegion.Name = "rangec"

Range("D4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("ranged").CurrentRegion.Name = "ranged"

Hi Eric,

One way, perhaps:

'=============
Public Sub Tester01()
Dim RngA As Range
Dim RngB As Range
Dim RngC As Range
Dim RngD As Range
Const sStr As String = "A4"

With Range(sStr)
Set RngA = .CurrentRegion.Columns(1)
Set RngB = .CurrentRegion.Columns(2)
Set RngC = .CurrentRegion.Columns(3)
Set RngD = .CurrentRegion.Columns(4)
End With
End Sub
'<<=============

---
Regards,
Norman


wrote in message
oups.com...
I'm still fiddling with this and thought I had the answer. The range
should just be the data in a particular column. When I use the
following macro, the range ends up being ALL of the data on the sheet.
Any ideas what I am doing wrong?

Sheets("Sheet2").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("rangea").CurrentRegion.Name = "rangea"


Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("rangeb").CurrentRegion.Name = "rangeb"

Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("rangec").CurrentRegion.Name = "rangec"

Range("D4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("ranged").CurrentRegion.Name = "ranged"

Norman, sorry to be simple minded but I ran that macro and it doesn't
appear that anything happened.

Eric

Hi Eric,

Norman, sorry to be simple minded but I ran that macro and it doesn't
appear that anything happened.

I suspect I misinterpreted your intentions. Try:

'=============
Public Sub Tester01A()

With Range("A4")
Names.Add Name:="RangeA", RefersTo:=.CurrentRegion.Columns(1)
Names.Add Name:="RangeB", RefersTo:=.CurrentRegion.Columns(2)
Names.Add Name:="RangeC", RefersTo:=.CurrentRegion.Columns(3)
Names.Add Name:="RangeD", RefersTo:=.CurrentRegion.Columns(4)
End With
End Sub
'<<=============

---
Regards,
Norman