+ Reply to Thread
Results 1 to 4 of 4

Convert matrix into ranges to be used as named ranges

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Convert matrix into ranges to be used as named ranges

    Ok, so I'm convinced loops are not my thing.

    So far I have the below which takes column B and creates a range in column F, but now I would like to look at column C and make a range in column G, and then D to H. Eventually I am going to use these ranges as named ranges for some data validation.

    Can I get a little assistance on how to get a loop going? Range("F1:H4") are the results desired.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jeffreybrown; 10-13-2010 at 02:45 PM.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Convert matrix into ranges to be used as named ranges

    jeffreybrown,


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    Please Login or Register  to view this content.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Convert matrix into ranges to be used as named ranges

    If interested - a formula driven approach attached

    If you do use VBA you might want to consider applying AutoFilter (based on "a") as you iterate columns - it will be quicker than iterating row by row.

    You could also load the data into an array removing blanks simultaneously.

    Generally speaking formulae normally suffice and are generally preferable as the DV lists will update as the source data is amended.
    The same can obviously be achieved with VBA but would in turn require use of an appropriate event (eg Change) to trap the update.
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Convert matrix into ranges to be used as named ranges

    @Stan,

    Thank you very much, works great.

    @Luke,

    Thank you for the formula approach which also works great and you're right the flexiblility with DV is outstanding. Once again, I blanked out on using the autofilter.

    Thanks, Jeff

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1