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?
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
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?
Code:
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
Thanks so much all.
Really appreciate you even reading all of this

Last edited: