Record macro to name multiple ranges

S

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
 
D

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.
 
V

Vergel Adriano

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top