Macro for dynamic named ranges

[hgeek23]

Im trying (for 2 days 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()
' written by Roger Govier, Technology4U
' enhanced by Lee Armitage, Heath Lambert Group (leearmitage@gmail.com)
    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 = 1

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

    ' 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:="=COUNTA($" &  Rowno & ":$" & Rowno & ")"
    wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNTA(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 of Module Technology4U/Lee Armitage"

End Sub

The last trick, which i'm completely baffled with, is to then replace the static ranges that are referenced in the table below the original table with the dynamic ranges. all of the ranges correctly correspond to the column, so it should just be an issue of substituting anything like "=COUNTIF(C$10:C$69,">1%")-COUNTIF(C$10:C$69,">2%")" to =COUNTIF(range1,">1%")-COUNTIF(range2,">2%")

If anyone can help i'd be extremely appreciative.

 

[Andrew Poulsom]

In my code instead of:

LR = .Range("A" & .Rows.Count).End(xlUp).Row

try:

LR = .Range("A10").End(xlDown).Row

 

[hgeek23]

Is there any way I can make the ranges variable, such that if i change the amount of headings than It will change the amount of columns, or if i changed the row numbers from 10 to X so would the LR range?

 

[hgeek23]

In my code instead of:

LR = .Range("A" & .Rows.Count).End(xlUp).Row

try:

LR = .Range("A10").End(xlDown).Row

I've tried your code and it doesn't seem to work. keeps getting variable or with block errors all over the place.

Tried the following and it's getting errors at the "rng.name" part

 Dim Sh As Worksheet, ws As Worksheet
    
    Dim LR As Long
    Dim c As Long
    Dim lcol As Long

    
    Dim Rng As Range
    ' 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

Const Colno = 1

Const Offset = 5

Set ws = ActiveSheet

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

        With Sh
            LR = Range("A10").End(xlDown).Row
            For c = Colno To lcol
                Set Rng = Range(Cells(Rowno, c), Cells(LR, c))
                If WorksheetFunction.CountA(Rng) > 0 Then
                    Rng.Name = "'" & .Name & "'!" & Replace(.Cells(1, c).Value, " ", "_")
                End If
            Next c
        End With
    



End Sub

The other code seems to be alot more dynamic when it comes to the names, but it never sets lrow correctly. What is wrong with these?

 

[Andrew Poulsom]

What errors are you getting. Changing that one line of code shouldn't cause any problems.

 

[hgeek23]

In your code, all lines with a . In front of a function. Then when removing the .'s the rng.name has the error. Which code should I use?

It's error 91 I believe.

 

[Andrew Poulsom]

The dot is there to qualify Name with the worksheet. You can't remove it.

 

[hgeek23]

Then is there any way we can just modify the earlier code posted (even if it is a lot longer)? As far as I can tell it has no issues with the exception of the length of the array

 

[hgeek23]

Andrew is there a way to modify the original code i posted so rather than counting the entire row, i need it to just count from row 10 until the first blank space?

 

[the_original_invisible]

You say you have a table underneath the last row of data. Is all that's in it the formulas mentioned here?

The last trick, which i'm completely baffled with, is to then replace the static ranges that are referenced in the table below the original table with the dynamic ranges. all of the ranges correctly correspond to the column, so it should just be an issue of substituting anything like "=COUNTIF(C$10:C$69,">1%")-COUNTIF(C$10:C$69,">2%")" to =COUNTIF(range1,">1%")-COUNTIF(range2,">2%")

If so, surely you'd be better off having that table at the top of your spreadsheet, in which case problem solved. Personally I never put my totals at the bottom, always at the top, as I don't want to have to scroll thru' x number of rows to find my totals

You then wouldn't need to used named ranges, you could just use

=COUNTIF(C$10:OFFSET(C$65536,-1,0),">1%")