+ Reply to Thread
Results 1 to 10 of 10

Got range of a cell, now modify that to be range of entire column

  1. #1
    Registered User
    Join Date
    08-08-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    15

    Got range of a cell, now modify that to be range of entire column

    I do a string check in order to locate a specific column heading. Let's say, for example, that the range address is $AB$1.

    This is saved to a range variable.

    So let's say

    range X is $AB$1

    Now I want to get the range of the entire column, so I want

    range Y to be $AB:$AB


    How do I get from X to Y?
    Thanks much!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Got range of a cell, now modify that to be range of entire column

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Got range of a cell, now modify that to be range of entire column

    Check the post has arrived ...

  4. #4
    Registered User
    Join Date
    08-08-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Got range of a cell, now modify that to be range of entire column

    Thanks Trevor...

    So simple. I like it, and I also hate it for the 10 minutes of thinking I did when there was such a simple tool available. That's life?

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Got range of a cell, now modify that to be range of entire column

    You're welcome. Thanks for the rep.


    I'm not entirely sure I like to be called a
    simple tool

    Regards, TMS

  6. #6
    Registered User
    Join Date
    08-08-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Got range of a cell, now modify that to be range of entire column

    Lol
    Ok, I need a tad more help I guess:
    I'm pasting this all here just to show the different things I've tried. I'm trying to understand what variable types I'm attempting to set, rather than guess and test, but I feel like it's getting worse as I try more things and they all fail...
    I can get this stuff to DISPLAY but am having a horrible time actually setting the xCOLUMN range variable.


    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-08-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Got range of a cell, now modify that to be range of entire column

    Wait, I got it...I guess I need to get educated on "Set":

    Please Login or Register  to view this content.
    Finally works. I guess one thing I still don't know though is why, when x (a range) is "$AB$1", the following returns the same range, x?


    Please Login or Register  to view this content.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Got range of a cell, now modify that to be range of entire column

    You see, this is what happens when you insult someone who has helped you ... you find you have to go back for more help.


    I don't really understand what it is you want to do but see if this helps:

    Please Login or Register  to view this content.

    Regards, TMS

  9. #9
    Registered User
    Join Date
    08-08-2012
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Got range of a cell, now modify that to be range of entire column

    Ah, the False and the Split were new to me, thank you!

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Got range of a cell, now modify that to be range of entire column

    You're welcome.

+ 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. [SOLVED] Cond. formatting a column/Range, highlight cell in column/range if equal to specific text
    By DFrank231 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2013, 03:35 PM
  2. [SOLVED] Marcro to copy range not entire column
    By slohman in forum Excel General
    Replies: 5
    Last Post: 06-19-2012, 03:32 AM
  3. Dim an Indirect Named Range as a cell and modify
    By blueice2627 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-10-2012, 01:27 PM
  4. [SOLVED] Macro to delete entire row (within a specified range) if cell in column A is empty
    By instinctellekt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2012, 10:01 AM
  5. how to call an entire column/range of cells
    By papermoon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2010, 01:31 PM

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