Multiple Dynamic Named Ranges across Columns


S

Steve

Hi all. I have been using the following formula to create dynamic named ranges:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

Where A1 is the column header of the data to be included in the named range.

Now I need to create dynamic named ranges from Column A thru Column CK. Is there a way to do this with code so I don't have to manually create each one? The data in row 1 is what I would like the name of the range to be.

Thanks in advance!
 
Ad

Advertisements

D

Don Guillett

Hi all. I have been using the following formula to create dynamic named ranges:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)



Where A1 is the column header of the data to be included in the named range.



Now I need to create dynamic named ranges from Column A thru Column CK. Is there a way to do this with code so I don't have to manually create each one? The data in row 1 is what I would like the name of the range to be.



Thanks in advance!

Should do it
Sub MakeColumnDefinedNamesSAS()
Dim i As Long
Dim lr As Long
For i = 1 To 89
lr = Cells(Rows.Count, i).End(xlUp).Row
Range(Cells(1, i), Cells(lr, i)).Name = "col" & i
Next i
End Sub
 
C

Claus Busch

Hi Steve,

Am Mon, 30 Jul 2012 16:17:45 -0700 (PDT) schrieb Steve:
Hi all. I have been using the following formula to create dynamic named ranges:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

Where A1 is the column header of the data to be included in the named range.

Now I need to create dynamic named ranges from Column A thru Column CK. Is there a way to do this with code so I don't have to manually create each one? The data in row 1 is what I would like the name of the range to be.

your first name is "Test". If not, change it in the code:

Sub myNames()
Dim LCol As Integer
Dim i As Integer

LCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To LCol
ActiveWorkbook.Names.Add Name:=Cells(1, i), _
RefersTo:="=OFFSET(Test,," & i - 1 & ")"
Next
End Sub


Regards
Claus Busch
 
Ad

Advertisements

D

Don Guillett

Hi all. I have been using the following formula to create dynamic named ranges:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)



Where A1 is the column header of the data to be included in the named range.



Now I need to create dynamic named ranges from Column A thru Column CK. Is there a way to do this with code so I don't have to manually create each one? The data in row 1 is what I would like the name of the range to be.



Thanks in advance!

That will not make each column dependent on the last cell in that column.
 

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