Dynamic named ranges macro

**hgeek** · 08-02-2010, 01:03 PM

Im trying (for 2 weeks now) to create a macro to create a set of dynamic named ranges. there are column headers in row 5 and 5 rows below that there is the beginning of my data.

Heres where the sticky part comes in: i have another table below my data that references the above table (that is why i need the dynamic named ranges). So, what i need the macro to probably do is to have it count until the first blank space in the column, starting at row 10.

I found code in another post (http://www.mrexcel.com/forum/showthread.php?t=432030) and adjusted for my own purposes: only issue is that the range is stopping about 8 rows short.
Here is the code:

  Option Explicit
Sub CreateNames_sample()
'
' CreateNames_sample Macro
' Create Dynamic named ranges in sample
'

'

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long

Dim myName As String, Start As String

Dim wsName As String
' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 5

' set the Offset as the number of rows below Rowno, where the
' data begins
Const Offset = 5

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1



' On Error GoTo CreateNames_Error

Set wb = ActiveWorkbook
Set ws = ActiveSheet

' count the number of columns used in the row designated to
' have the header names

lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address


'replace blanks in worksheet names with underscore for the purposes of adding range names
wsName = ws.Name
wsName = Replace(wsName, " ", "_")

wb.Names.Add Name:=wsName & "_lcol", RefersTo:="=COUNT($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & ")"
wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & wsName & "_lrow," & wsName & "_lcol)"

For i = Colno To lcol
' if a column header contains space or other invalid character etc, replace with underscore
myName = Replace(Cells(Rowno, i).Value, "/", "_")
myName = Replace(myName, " ", "_")
myName = Replace(myName, "&", "_")
myName = Replace(myName, "(", "_")
myName = Replace(myName, ")", "_")
myName = Replace(myName, "?", "_")
myName = Replace(myName, "\", "_")

If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)"

nexti:
Next i

On Error GoTo 0
MsgBox "All dynamic Named ranges have been created"
Exit Sub

CreateNames_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames"

End Sub

The code given is the original code, unmodified. I am utterly perplexed as to how to fix it.
The issue is that my sheet has multiple tables on top of each other, not just one set of data, so rather than counting the entire row, i need it to just count from row 10 until the first blank space.

If anyone could help i'd be very grateful.

**Bob Phillips** · 08-02-2010, 01:57 PM

I don't see any dynamic named ranges. Post a workbook and tell me what names are wrong and in what way.

**hgeek** · 08-03-2010, 10:12 AM

Thanks for your help. attached is a sample workbook

sample.xlsm

All the created names are wrong, they are not the correct length.

**hgeek** · 08-03-2010, 11:42 AM

I have modified the code. Lrow and lcol don't seem to have a problem anymore. what seems to be the problem is when it is actually creating the parameters for the "refers to" box of the add-dynamic-name part, it is using an "index" function that is somehow chopping off 8 rows of data.

Any ideas?

Sub tryone()
'
'
' Create Dynamic named ranges
'

'

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long

Dim myName As String, Start As String

Dim wsName As String
' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 5

' set the Offset as the number of rows below Rowno, where the
' data begins
Const Offset = 5

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1

TK = Rowno + Offset

' On Error GoTo CreateNames_Error

Set wb = ActiveWorkbook
Set ws = ActiveSheet

' count the number of columns used in the row designated to
' have the header names

lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(TK, Colno).End(xlDown).Row
Start = Cells(Rowno, Colno).Address


'replace blanks in worksheet names with underscore for the purposes of adding range names
wsName = ws.Name
wsName = Replace(wsName, " ", "_")

wb.Names.Add Name:=wsName & "_lcol", RefersTo:="=COUNT($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & ")"
wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & wsName & "_lrow," & wsName & "_lcol)"

For i = Colno To lcol
' if a column header contains space or other invalid character etc, replace with underscore
myName = Replace(Cells(Rowno, i).Value, "/", "_")
myName = Replace(myName, " ", "_")
myName = Replace(myName, "&", "_")
myName = Replace(myName, "(", "_")
myName = Replace(myName, ")", "_")
myName = Replace(myName, "?", "_")
myName = Replace(myName, "\", "_")

If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)"

nexti:
Next i

On Error GoTo 0
MsgBox "All dynamic Named ranges have been created"
Exit Sub

CreateNames_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames"

End Sub

**hgeek** · 08-03-2010, 12:03 PM

Originally Posted by hgeek

I have modified the code. Lrow and lcol don't seem to have a problem anymore. what seems to be the problem is when it is actually creating the parameters for the "refers to" box of the add-dynamic-name part, it is using an "index" function that is somehow chopping off 8 rows of data.

Any ideas?

Sub tryone()
'
'
' Create Dynamic named ranges
'

'

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long

Dim myName As String, Start As String

Dim wsName As String
' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 5

' set the Offset as the number of rows below Rowno, where the
' data begins
Const Offset = 5

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1

TK = Rowno + Offset

' On Error GoTo CreateNames_Error

Set wb = ActiveWorkbook
Set ws = ActiveSheet

' count the number of columns used in the row designated to
' have the header names

lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(TK, Colno).End(xlDown).Row
Start = Cells(Rowno, Colno).Address


'replace blanks in worksheet names with underscore for the purposes of adding range names
wsName = ws.Name
wsName = Replace(wsName, " ", "_")

wb.Names.Add Name:=wsName & "_lcol", RefersTo:="=COUNT($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & ")"
wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & wsName & "_lrow," & wsName & "_lcol)"

For i = Colno To lcol
' if a column header contains space or other invalid character etc, replace with underscore
myName = Replace(Cells(Rowno, i).Value, "/", "_")
myName = Replace(myName, " ", "_")
myName = Replace(myName, "&", "_")
myName = Replace(myName, "(", "_")
myName = Replace(myName, ")", "_")
myName = Replace(myName, "?", "_")
myName = Replace(myName, "\", "_")

If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)"

nexti:
Next i

On Error GoTo 0
MsgBox "All dynamic Named ranges have been created"
Exit Sub

CreateNames_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames"

End Sub

I found out the issue: the index is functioning but when using the lrow function in the equation, when inputted into the worksheet it, instead of lrow operating as 75 as it should, it is operating as 68. This is wierd since under the locals window of the macro screen lrow is shown as 75, but when i put in the worksheet =lrow it is displayed as 68.

Any idea why this is?

**hgeek** · 08-03-2010, 02:00 PM

I figured out the issue, thank you for providing the help.

The final code is:

Sub tryone()
'
'
' Create Dynamic named ranges
'

'

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long

Dim myName As String, Start As String

Dim wsName As String
' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 5

' set the Offset as the number of rows below Rowno, where the
' data begins
Const Offset = 5

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1

TK = Rowno + Offset

' On Error GoTo CreateNames_Error

Set wb = ActiveWorkbook
Set ws = ActiveSheet

' count the number of columns used in the row designated to
' have the header names

lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
frow = ws.Cells(TK, Colno).End(xlDown).Row
Start = Cells(Rowno, Colno).Address


'replace blanks in worksheet names with underscore for the purposes of adding range names
wsName = ws.Name
wsName = Replace(wsName, " ", "_")

wb.Names.Add Name:=wsName & "_lcol", RefersTo:="=COUNT($" & Rowno & ":$" & Rowno & ")"
'wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & ")"
wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(" & "R" & TK & "C" & Colno & ":" & "R" & frow & "C" & Colno & ")"
wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & frow & "," & wsName & "_lcol)"
'wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & wsName & frow & "," & wsName & "_lcol)"

For i = Colno To lcol
' if a column header contains space or other invalid character etc, replace with underscore
myName = Replace(Cells(Rowno, i).Value, "/", "_")
myName = Replace(myName, " ", "_")
myName = Replace(myName, "&", "_")
myName = Replace(myName, "(", "_")
myName = Replace(myName, ")", "_")
myName = Replace(myName, "?", "_")
myName = Replace(myName, "\", "_")

If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & frow & ")"

nexti:
Next i

On Error GoTo 0
MsgBox "All dynamic Named ranges have been created"
Exit Sub

CreateNames_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames"

End Sub