Creating Named Ranges using VBA

[Mark BMS]

I am trying to create a For/Next loop which, after a csv data import will name Columns of data in the format "Meter1, Meter 2..... up to the last used column and the last used row as these may change depending on the imported data.

For testing I have created a simple workbook with a single sheet (Sheet1) with data in 13 columns and 26 rows. I recorded a macro whilst I named "A2:A26" which gave me the following code:-

Sub NamingRecord()
    ActiveWorkbook.Names.Add Name:="Name1", RefersToR1C1:="=Sheet1!R2C2:R26C2"
End Sub

I have tried to use this code with a number of variables to loop through the columns with data, this is my effort:-

ub AutoName()

Dim RE As Integer 'End Row
Dim RS As Integer 'Start Row
Dim CE As Integer 'End Column
Dim CS As Integer 'Start Column
Dim N As Long
Dim M As Long

RS = 2 'not interested in the first row
CS = 2 'not interested in the first column
'Find the last row with data
RE = Sheets(1).Range("A1").End(xlDown).Row
'Find the last column with data
CE = Range("A" & CS).End(xlToRight).Column

For N = CS To CE 'loop range defined by variables
    M = N - 1 'name variable number will be 1 less than the first column eg Column 2 named range will be Meter1

        ActiveWorkbook.Names.Add Name:="Meter" & M, RefersToR1C1:= _
        "=Sheet1!R & RS C & CS:R & RE C & CE" 'THIS IS THE LINE I THINK IS WRONG
Next N

End Sub

When executing the code, the correct number of named ranges are added to the name manager but the ranges they refer to are completely wrong.

This is what the range in the name manager looks like for one of the ranges "=Sheet1!32:32 & RS Sheet1!F:F & CS:Sheet1!32:32 & RE Sheet1!F:F & CE"

I am trying to substitute the numbers in the "Sheet1!R2C2:R26C2" part of the code with variables but have clearly got it horribly wrong.

I would be grateful if someone could give me some pointers on how to make this work

 

[John_w]

You can construct the R1C1 sheet name and cells string, but it's easier to specify the range of cells and then Excel includes the sheet name automatically. Here's both methods:

Public Sub Create_Column_Named_Ranges()

    Dim RE As Long 'End Row
    Dim CE As Long 'End Column
    Dim col As Long
    
    With ActiveSheet
        RE = .Range("A1").End(xlDown).Row
        CE = .Range("A2").End(xlToRight).Column
        For col = 2 To CE
            ActiveWorkbook.Names.Add Name:="Meter" & col - 1, RefersToR1C1:=.Range(.Cells(2, col), .Cells(RE, col))
            ActiveWorkbook.Names.Add Name:="xMeter" & col - 1, RefersToR1C1:="=" & .Name & "!R2C" & col & ":R" & RE & "C" & col
        Next
    End With

End Sub

 

[mikerickson]

The easiest way to name a range is to use the .Name property of a range object.

Range("A1:B10").Name = "myNamedRange"

In your case

For col = CS to CE
    Columns(CS).Cells(2,1).Resize(RE -  1, 1).Name = "Meter" & (col - 1)
Next Col

 

[Mark BMS]

You can construct the R1C1 sheet name and cells string, but it's easier to specify the range of cells and then Excel includes the sheet name automatically. Here's both methods:

Public Sub Create_Column_Named_Ranges()

    Dim RE As Long 'End Row
    Dim CE As Long 'End Column
    Dim col As Long
   
    With ActiveSheet
        RE = .Range("A1").End(xlDown).Row
        CE = .Range("A2").End(xlToRight).Column
        For col = 2 To CE
            ActiveWorkbook.Names.Add Name:="Meter" & col - 1, RefersToR1C1:=.Range(.Cells(2, col), .Cells(RE, col))
            ActiveWorkbook.Names.Add Name:="xMeter" & col - 1, RefersToR1C1:="=" & .Name & "!R2C" & col & ":R" & RE & "C" & col
        Next
    End With

End Sub

Many thanks for your answer. With a small tweak to the RE variable to look "B1" as I have a blank cell in "A1" it worked perfectly for me.