Multiple Dynamic Named Ranges across Columns

  • Thread starter Thread starter Steve
  • Start date Start date
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!
 
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!
Click to expand...

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
 
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.
Click to expand...

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
 
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!
Click to expand...

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