Record macro to name multiple ranges
 

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

Record macro to name multiple ranges
 

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

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

sancht wrote:

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

Record macro to name multiple ranges
 

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


"sancht" wrote:

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