+ Reply to Thread
Results 1 to 3 of 3

VBA challenge how to use the .Area to be able to encompass non-adjacent column into this?

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Question VBA challenge how to use the .Area to be able to encompass non-adjacent column into this?

    I have this code below. this code creates named range for columns that are selected meaning on selection.

    but the selection has one big problem. if you select columns that are next to eachother then it works.
    but if the you select non-adjacent columns then VBA only creates named range first one and ignores all other selected columns.

    in this attached example. i selected column B and column Dthen, i run the macro. it should have 4 named ranges. two helper named ranges Sheet1 =Sheet1!$1:$1048576 and Sheet1Lrow = =LOOKUP(9.99999999999999E+307,1/(1-ISBLANK(Sheet1!$A:$A)),ROW(Sheet1!$A:$A)) up to here it works fine. but then it also should create two named ranges one of column B "Sheet1Month" and another named range from column D "Sheet1Amount" becuase i selected two columns.
    the macro is currently crearting 3 named ranges and it misses the other column D.

    i have uploaded the sample file in this thread. I also recorded a few second video that shows the problem link of video in dropbox https://www.dropbox.com/s/1dviz5e9s3...Video.mp4?dl=0 i could not upload mp4 file here, so i used dropbox.

    i would greatly appreciate any help. I spent hours and i could not find a solution. I hope one of you geniuses could help me find the solution.

    Thank you very much.




    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: VBA challenge how to use the .Area to be able to encompass non-adjacent column into t

    Perhaps this (untested)
    Please Login or Register  to view this content.
    in place of this
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Re: VBA challenge how to use the .Area to be able to encompass non-adjacent column into t

    @xlnitwit

    this is brilliant! thanks a million.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. A Challenge: 100% Stacked area chart
    By ajm123456789 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-02-2012, 02:40 PM
  2. How to find the begining col number of adjacent area
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2011, 01:04 PM
  3. Varifying whether 2 imes encompass a time band
    By fraserjamie in forum Excel General
    Replies: 4
    Last Post: 09-25-2008, 06:28 AM
  4. What do { } brackets mean when they encompass a function?
    By BedeviledByBrackets in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2006, 10:40 PM
  5. Replies: 6
    Last Post: 03-04-2006, 07:30 AM
  6. [SOLVED] Non-adjacent area selection
    By rjamison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2005, 08:05 PM
  7. Re: Non-adjacent area selection
    By rjamison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2005, 08:05 PM
  8. [SOLVED] Non-adjacent area selection
    By Petr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2005, 10:06 AM

Tags for this Thread

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