+ Reply to Thread
Results 1 to 13 of 13

If any cell a sheet contains specific text then select column to last used row

  1. #1
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    If any cell a sheet contains specific text then select column to last used row

    Hi everyone,

    I have the following code which isn't working at all:

    Please Login or Register  to view this content.
    What I'm trying to do, is that if any cell in the worksheet contains the text "Final" then select the column to the last cell with data. There will be more than one column that has a cell which contains Final, so all columns that have that text should be selected.

    Any ideas?

    Thanks!!

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If any cell a sheet contains specific text then select column to last used row

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If any cell a sheet contains specific text then select column to last used row

    Or:

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: If any cell a sheet contains specific text then select column to last used row

    Hi John,

    Thanks. I've tried it, and the code appears to find the last usedrange of the last column that contains the text Final, but it doesn't actually select all the columns that contain a cell with that string in the worksheet. Any ideas?

    Thanks!

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If any cell a sheet contains specific text then select column to last used row

    That's all it will do (is select via the loop), unless you tell the code too 'DO SOMETHING. What are you trying too achieve.

  6. #6
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: If any cell a sheet contains specific text then select column to last used row

    I'm trying to get the code to select the entire column if any of the cells in the columns contain the word Final. Then the macro will run another bit of code which calls an excel extension.

  7. #7
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: If any cell a sheet contains specific text then select column to last used row

    Hi John,

    So the following code selects the entire column of the last instances of Final, but it doesn't select any of the other ones:

    Please Login or Register  to view this content.
    Any idea how I can amend this code to select EVERY column that has the text Final in it?

    Thanks!!

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If any cell a sheet contains specific text then select column to last used row

    It's selecting each column but you have to tell it what to do once it selects otherwise it will continue too loop until the end

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: If any cell a sheet contains specific text then select column to last used row

    Hi John, sorry, I need to have all the columns selected at the same time. I wasn't clear enough on that point

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If any cell a sheet contains specific text then select column to last used row

    Try:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: If any cell a sheet contains specific text then select column to last used row

    Or
    This should be an easy to understand way.
    Please Login or Register  to view this content.
    There are better ways, maybe with Union or as I see now, John's way, but if your Range is not too big, this should do for now.
    Last edited by jolivanes; 07-11-2016 at 02:39 PM.

  12. #12
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: If any cell a sheet contains specific text then select column to last used row

    That's it. Thanks very much, John. I'm afraid I don't understand the y variable lines. If you have a moment, can you explain what they do?

    Thanks!

  13. #13
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If any cell a sheet contains specific text then select column to last used row

    The y variable catches the cell 1 value each time "Final" is found. To see how it works you can add a message box in the loop

    Please Login or Register  to view this content.
    The final y will represent the range of each column which has the work "Final". Then you select the EntireColumn all at once. Hope that makes sense.

+ 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. Select Cell Based on Specific Text
    By StudioLETA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-15-2015, 04:50 PM
  2. VBA to select next cell(s) with specific text
    By jhuvba in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-16-2015, 03:35 PM
  3. [SOLVED] Copy rows with specific text in specific column into specific sheet
    By Valemaar in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-22-2014, 03:23 PM
  4. [SOLVED] select last row containing specific text in column
    By hopefulhart in forum Excel General
    Replies: 3
    Last Post: 06-01-2014, 07:11 AM
  5. Replies: 3
    Last Post: 12-12-2013, 12:27 PM
  6. Select specific text from one cell and move to another cell
    By Cliffavon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-02-2013, 04:08 AM
  7. [SOLVED] Select cell containing specific text &return value from another ce
    By plf100 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-16-2005, 10:00 AM

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