Record macro to name multiple ranges

[sancht]

Hi

I have an excel sheet with over 50 columns. I was wondering if I could
write a macro (i'm a novice programmer) or record one to create name
ranges on each column. I am using the offset and count function to
name the ranges as I would be adding new rows (to the same columns)
every few days.

I hope someone cal help me.

Thanks,

ST

 

[Dave Peterson]

You may get some sample code that does this...

But if you explain what you're doing, you may find you get alternate
suggestions.

 

[Guest]

The macro will create named ranges for columns 1 through 50 in Sheet1. Named
ranges will be named as ColumnX, where X is the column number.

Sub NameColumns1to50()

Dim i As Integer
Dim strEntireColumn As String
Dim strReferenceCell As String

For i = 1 To 50
strReferenceCell = Cells(1, i).Address
strEntireColumn = Range(Cells(1, i), Cells(65536, i)).Address
ThisWorkbook.Names.Add "Column" & i, "=OFFSET(Sheet1!" &
strReferenceCell & ",0,0,COUNTA(Sheet1!" & strEntireColumn & "),1)"
Next i

End Sub