Solved

Excel VBA name range

Posted on 2015-01-19
2
91 Views
Last Modified: 2015-01-19
Hello,

please see attached file.

somehow the dynamic range does not work always. therefore i wanted help how to change the code
 to do the following

Define BigStr as referring to:  =REPT("z",255)
 
Define Icol as referring to:=MATCH(BigStr,Details!$1:$1)  instead of =COUNTA(Sheet1!$1:$1)
 
Define lrow  as referring to: =MATCH(BigStr,Details!$A:$A)  instead of =COUNTA(Sheet1!$A:$A)


Sub CreateNames()
' written by Roger Govier, Technology4U

    Dim wb As Workbook, WS As Worksheet
    Dim lrow As Long, lcol As Long, I As Long
    Dim myName As String, Start 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 ROffset = 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
    
    ' Set an Offset from the starting column, for the column number that
    ' will always have data entered, and will therefore be used in calculating lrow
    
    Const COffset = 0  ' in this case, the first column will always contain data.

    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 = Cells(Rowno, Columns.Count).End(xlToLeft).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 + COffset & ")"
    wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$1048576," & "lrow," & "Lcol)"

    For I = Colno To lcol
        ' if a column header contains spaces, replace the space with an underscore
        ' spaces are not allowed in range names.
        myName = Replace(Cells(Rowno, I).Value, " ", "_")
        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:=myName, RefersToR1C1:= _
                     "=R" & Rowno + ROffset & "C" & I & ":INDEX(C" & I & ",lrow)"
nexti:
    Next I

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

    Exit Sub

CreateNames_Error:

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

End Sub

Open in new window

NameRange.xlsm
0
Comment
Question by:Flora
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40557499
Small tweaks only:

    wb.Names.Add Name:="BigStr", RefersTo:="=REPT(""z"",255)"
    wb.Names.Add Name:="lcol", RefersTo:="=MATCH(BigStr,$" & Rowno & ":$" & Rowno & ")"
    wb.Names.Add Name:="lrow", RefersToR1C1:="=MATCH(BigStr,C" & Colno + COffset & ")"
    wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$1048576,lrow,Lcol)"

Open in new window


A Table would be simpler though.
0
 
LVL 5

Author Closing Comment

by:Flora
ID: 40557779
you are so cool Rory.

I would have been lucky having at least 20% of the capacity of your intelect :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Excel Formula 4 28
Sum iF  based on a null cell 11 29
remove lower case characters in excel formula 12 40
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now