Automatically define a Variable Range using a macro

[Abgar]

Hi Guys,

I have asked this before but it wasn't really necessary at the time, so i didn't push it, but now it would be REALLY handy.
Theres a few points to this, so I'll make it easy to read

1. I need to define a variable range of all the USED cells in a column (starting at row 2 as the row 1 is the header)

2. I need to do this for columns A through to P inclusively (all using the same lastrow reference as that of column A)

3. I need to call the range name by the contents of the header in that column (Row 1)

4. Some of the data in the header row (row 1) contains spaces - can we change this to an underscore for the range name only?

5. Does a range only work for the active worksheet? Because i will have multiple worksheets, all with the exact same information in row 1, but different numbers of rows (and different data as well), but i will need the range defined on each worksheet......

I've found the following code and thought it was it, but it only defines on 1 worksheet, and for some reason only defines ranges for columns 1 and 2. Also it defines the range as A1:A65535 - not the USED range.....
Can anyone help me with this?

Sub Ranges()

Sheets("Shee1").Select
    
        Dim wb As Workbook, ws As Worksheet
        Dim lrow As Long, lcol As Long, i As Long
        Dim myName As String, Start As String
        Const Rowno = 1
        Const Offset = 1
        Const Colno = 1
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet
        lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
        lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
        Start = Cells(Rowno, Colno).Address
                wb.Names.Add Name:="lcol", _
                 RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
            wb.Names.Add Name:="lrow", _
                 RefersToR1C1:="=COUNTA(C" & Colno & ")"
            wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"

        For i = Colno To lcol
        myName = Replace(Cells(Rowno, i).Value, " ", "_")
        If myName = "" Then
           MsgBox "Fatal Error"
            Exit Sub
        End If
         wb.Names.Add Name:=myName, RefersToR1C1:= _
             "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
nexti:
        Next i
End Sub

And lastly (I promise) if anyone CAN help me with any of the above, would it be too much to ask to also make a note about what each section of the code actually does? Just so that i can understand it for future use and that way, i can actually learn something from it, instead of just utilising it..

Thanks so much all.

Really appreciate you even reading all of this

 

[Abgar]

Thanks Andrew,

That works marvelously

I tested it on multiple worksheets and it all worked beautifully except for one.
It got half way through naming the ranges and then threw up an error saying "That name is not valid".
All of the data looked right (with correct headings etc), but it just stopped.
Any ideas what type of thing i could look for that would give that error?

Thanks as always.

 

[Andrew Poulsom]

Maybe the name looks like a cell reference, eg A1 or R1C1.

 

[Abgar]

No it shouldn't be the range names, because they are exactly the same on every worksheet....

 

[Andrew Poulsom]

What was the name of the worksheet and the value of the cell when the macro failed?

 

[Abgar]

Hi Andrew,

I figured it out.
On some of the worksheets there is an extra column of data which i had forgotten about, and so hadn't put in a Header cell for that column.
Once i put that it, it runs perfectly

Thanks so much again for all your help mate