Loop Through columns and make Named Ranges

[GeeWhiz7]

Hi there,
I'm trying to adapt something that Tonyyy and This is my Answer helped me with earlier.

I have a worksheet that has columns of data.
I would like to loop through the columns starting at the second one (B) and create a named range for the last 10 cells in each column.
So column 2 might get "_Range2" as a name for example.

Data looks like this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Price 1	Price 2
2	12/26/2021	16	27
3	12/27/2021	12	27
4	12/28/2021	17	24
5	12/29/2021	15	26
6	12/30/2021	26	25
7	12/31/2021	17	24
8	1/1/2022	21	25
9	1/2/2022	13	26
10	1/3/2022	26	27
11	1/4/2022	25	26
12	1/5/2022	15	27
13	1/6/2022	22	25
14	1/7/2022	18	27
15	1/8/2022	17	26
16	1/9/2022	26	25
17	1/10/2022	25	25
18	1/11/2022	22	25
19	1/12/2022	22	27
20	1/13/2022	13	24
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()-10
A3:A20	A3	=A2+1

Ignore the formulas as I was just putting some random data in there.

The code I've tinkered with is here, but I can't quite get it to work.
I always have the same number of columns and would like to have named ranges for even blank column data.

Sub RangeNamer()

Dim rng As Range
Dim y As Long
Dim z As Long
Dim RangeRef As Range
Dim rCount As Long

Sheets("Sheet1").Activate

y = 2    'price data starting in column 2
z = 1    'would like to name first column of price data as _Tick1 hence the 1

rCount = Cells(Rows.Count, 1).End(xlUp)

For Each rng In Range("B1:AY1").Columns

    Set RangeRef = Cells(rCount, y).End(xlUp).Offset(-10).Resize(11, 1)

    ThisWorkbook.Names.Add Name:="_Tick" & z, RefersTo:=RangeRef
   
z = z + 1
y = y + 1
Next rng
 
End Sub

Thanks!

 

[BSALV]

Sub RangeNamer()

     Dim rng   As Range
     Dim z     As Long
     Dim RangeRef As Range
     Dim rCount As Long

     With Sheets("Sheet1")
          z = 1                                                 'would like to name first column of price data as _Tick1 hence the 1
          rCount = Cells(Rows.Count, 1).End(xlUp).Row           'last used row in column A

          For Each rng In .Range("B1:AY1").Cells                'loop through all this cells
               With .Cells(rCount, rng.Column).End(xlUp)        'starting from row rCount CTRL+up
                    Set RangeRef = .Offset(Application.Max(1 - .Row, -10)).Resize(11, 1)     'make sure to start at least in row 1 !!!!
                    'MsgBox RangeRef.Address
                    RangeRef.Name = "_Tick" & z
               End With
               z = z + 1
          Next rng
     End With
End Sub

 

[BSALV]

sorry, mistaken ! 2nd version

Sub RangeNamer()
     Dim RangeRef As Range, c As Range

     With Sheets("Sheet1")
          Set c = .Cells(Rows.Count, 1).End(xlUp)
          Set RangeRef = c.Offset(Application.Max(2 - c.Row, -10)).Resize(Application.Min(11, Application.Max(1, c.Row - 1)))     'caution if number of rows <10
          MsgBox RangeRef.Address
          For Each rng In .Range("B1:AY1").Cells                'loop through all this cells
               RangeRef.Offset(, rng.Column - 1).Name = "_Tick" & rng.Column - 1
          Next rng
     End With
End Sub

 

[DanteAmor]

Try this:

Sub RangeNamer()
  Dim rng As Range, RangeRef As Range
  Dim y As Long, z As Long, rCount As Long
  
  Sheets("Sheet1").Activate
  z = 1    'would like to name first column of price data as _Tick1 hence the 1
  rCount = Range("A" & Rows.Count).End(xlUp).Row
  If rCount > 9 Then
    For y = 2 To Columns("AY").Column
      Set RangeRef = Cells(rCount, y).Offset(-9).Resize(10, 1)
      ThisWorkbook.Names.Add Name:="_Tick" & z, RefersTo:=RangeRef
      z = z + 1
    Next
  End If
End Sub

 

[GeeWhiz7]

Try this:

Sub RangeNamer()
  Dim rng As Range, RangeRef As Range
  Dim y As Long, z As Long, rCount As Long
 
  Sheets("Sheet1").Activate
  z = 1    'would like to name first column of price data as _Tick1 hence the 1
  rCount = Range("A" & Rows.Count).End(xlUp).Row
  If rCount > 9 Then
    For y = 2 To Columns("AY").Column
      Set RangeRef = Cells(rCount, y).Offset(-9).Resize(10, 1)
      ThisWorkbook.Names.Add Name:="_Tick" & z, RefersTo:=RangeRef
      z = z + 1
    Next
  End If
End Sub

Both BSALV and Dante’s methods work so I’m just selecting one for the solution. I’m learning quite a bit just from the replies and it’s interesting so see how people approach things differently sometimes.

Tonyyy pointed out that my original post of code works if I removed the .End(xlUp) from the RangeRef Set line (already done pre loop) and added .Rows to the end.

Thanks guys!

 

[DanteAmor]

Glad we could help & thanks for the feedback

It could be simplified like this:

Sub rngname()
  Dim c As Range
  For Each c In Range("B1:AY1")
    ThisWorkbook.Names.Add "_Tick" & c.Column - 1, c.Cells(Range("A" & Rows.Count).End(3).Row).Offset(-9).Resize(10, 1)
  Next
End Sub