Excel VBA - Printing Out Named Ranges


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Excel VBA - Printing Out Named Ranges

  1. #1
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666

    Excel VBA - Printing Out Named Ranges

    In order to compensate for the fact that things keep getting moved around on some spreadsheets I have, which messes up specific cell locations that I need to manipulate, I created "Named Ranges" for all of these locations.

    97 named ranges to be exact, and I now I just need to change the existing code that I have to use the named ranges instead of the hard coded cell locations.

    My question: How can I get a print out of all named ranges on a specific spreadsheet? Having something like that to refer to would make reworking the code I have a lot smoother.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Sub ListOutAllRangeNames()
    Sheets.Add
    Range("A1").ListNames
    Columns("A:B").Columns.AutoFit
    End Sub
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I get a Run Time error of 1004 (which seems to be the error number Excel throws when it doesn't know what else to do) but the important part is the text which is: "Can not change part of merged cell."

    Range("A1").ListNames is highlighted.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    That's odd, I can't repro the error. Was a new Worksheet added to your Workbook? ListNames should just list all the Range names in column A and the corresponding range values in column B of the new Worksheet. Maybe you need to fully qualify the object references:

    Code:
    Sub ListOutAllRangeNames()
    
    Dim x As Worksheet
    
    Set x = Sheets.Add
    x.Range("A1").ListNames
    x.Columns("A:B").Columns.AutoFit
    
    Set x = Nothing
    
    End Sub
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Bada Bing Bada Boom....that did it.

    Thank you sir!
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Excel VBA - Reshow A UserForm
    By Hack in forum VB Classic
    Replies: 1
    Last Post: 09-10-2008, 03:40 PM
  2. Replies: 0
    Last Post: 05-15-2008, 05:24 PM
  3. Replies: 1
    Last Post: 01-02-2007, 09:58 AM
  4. Workbook not Closing Properly by Excel VBA
    By blayne in forum VB Classic
    Replies: 1
    Last Post: 11-17-2005, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center