+ Reply to Thread
Results 1 to 18 of 18

Using a macro to select rows with a specific word in a column

  1. #1
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Using a macro to select rows with a specific word in a column

    Hello all,
    I have a questions concerning how a macro would be able to select a specific range of columns if a cell in column A contained the word total.

    For example:

    Column A:
    J1900
    J1900
    J1900
    J1900
    J1900 Total
    J1953
    J1953
    J1953 Total

    I would want the rows containing the word 'Total' in them to be selected (columns A:K) so I could add borders and bold the text of the selection.

    If there are any questions on what I'm trying to do, I could create a quick workbook showing the desired functionality,

    Thank you

  2. #2
    Forum Contributor
    Join Date
    07-24-2012
    Location
    Brentwood, TN
    MS-Off Ver
    Excel 2007 at home/2010 at work
    Posts
    228

    Re: Using a macro to select rows with a specific word in a column

    If the word "Total" is in column A, with the original 5 numbers, then this might work.

    Please Login or Register  to view this content.
    Last edited by LaffyAffy13; 07-15-2013 at 03:01 PM. Reason: Fixed information
    ~~LaffyAffy13~~

    If I have helped you solve your problem, please be sure to click at the bottom left of my post.

    Thanks.

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using a macro to select rows with a specific word in a column

    ahilty,

    Here are three ways of doing so. I personally prefer the first method, but any of the three should work for you.

    Method 1, using Range.Find:
    Please Login or Register  to view this content.


    Method 2, using Range.Autofilter:
    Please Login or Register  to view this content.


    Method 3, the "one-liner" (I have additional lines to prevent errors, but it could be written as a one-liner if you really wanted. I would not recommend it though)
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Using a macro to select rows with a specific word in a column

    LaffyAffy,
    I tried this but it only selected the first cell that contained the word Total and added the borders. It didn't select the row through column K and it didn't do the formatting to any other cell with the word total in it.

    Tiger,
    I will be attempting yours currently.
    Thanks you both for the help.

  5. #5
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Using a macro to select rows with a specific word in a column

    Tiger, (I used the first option) The message box correctly showed the ranges that needed to be formatted. Where would I input the following code unless you have a better suggestion on how to apply it:

    Please Login or Register  to view this content.
    The above code is the recorded macro to change the selected text to bold and then add a single top border to the range "A#:K#" and a double bottom border to the same range.
    Last edited by ahilty; 07-15-2013 at 03:14 PM. Reason: Forgot to mention which code I used

  6. #6
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Using a macro to select rows with a specific word in a column

    I was putting this one together as others posted (including yourself).

    See if it works for you...

    Please Login or Register  to view this content.

  7. #7
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using a macro to select rows with a specific word in a column

    ahilty,

    The completed code would look like this:
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    07-24-2012
    Location
    Brentwood, TN
    MS-Off Ver
    Excel 2007 at home/2010 at work
    Posts
    228

    Re: Using a macro to select rows with a specific word in a column

    ahility, sorry about that. I noticed that problem too, so I edited my code as quickly as I could, but I guess you started using it before I got the chance to finish editing it. I also didn't know how many rows of data you have, so I just put 5000 as a guess. you can change that too, but tigeravatar is much more experienced in excel and I'm sure his method works correctly

  9. #9
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Using a macro to select rows with a specific word in a column

    Workbook Macro Test.xlsm
    I am attaching a test workbook so you can see what I would like the end result to be.
    Thank you all for helping.

    Also, if anyone knows how to get rid of that column header error, let me know (it's annoying an may confuse a novice user of this spreadsheet).

  10. #10
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Using a macro to select rows with a specific word in a column

    jhren,
    Wow, that worked wonders. Thank you very much for the help.
    I'm going to try and add a few more features such as text size and backgrounds and whatnot but that did exactly what I asked for it to do.

    Thank you all. Reputation Added to all.

    Tiger,
    Your updated code also did exactly as asked. Thank you very much
    Last edited by ahilty; 07-15-2013 at 03:39 PM. Reason: Tried another users code and wanted to give them credit

  11. #11
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Using a macro to select rows with a specific word in a column

    I posted before I saw your recorded macro.

    Here's my code to add double-line bottom border (note weight is not required as double-line only has one weight)...

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Using a macro to select rows with a specific word in a column

    jhren or Tiger,
    Do you know a way to insert a row below the row that was selected (to create space between to total row and the next items rows)?
    If it isn't an easy fix, then don't worry about it. I was just curious.

  13. #13
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Using a macro to select rows with a specific word in a column

    Quote Originally Posted by ahilty View Post
    jhren or Tiger,
    Do you know a way to insert a row below the row that was selected (to create space between to total row and the next items rows)?
    If it isn't an easy fix, then don't worry about it. I was just curious.
    After you've done all your formatting to the "total" row, insert the following line in the code, before the first "End With"...

    Please Login or Register  to view this content.

  14. #14
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using a macro to select rows with a specific word in a column

    it is as simple as jhren states. Here is the updated code with the insert line:
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Using a macro to select rows with a specific word in a column

    Tiger, It also worked. Thanks for the help.

    jhren, Thank you very much.

    If either of you open the test workbook and run the macro by pushing the button on sheet 2, I cannot seem to get rid of the prompt that appears concerning the column headers for the sorting. Any ideas?
    Last edited by ahilty; 07-15-2013 at 04:00 PM. Reason: spelling mistake, tried Tigers code and thanked him.

  16. #16
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Using a macro to select rows with a specific word in a column

    Quote Originally Posted by ahilty View Post
    Tiger, I will give this a try. Thanks for the help.

    jhren, Thank you very much.

    If either of you open the test workbook and run the macro by pushing the button on sheet 2, I cannot seem to get rid of the prompt that appears concerning the column headers for the sorting. Any ideas?
    The only "prompt" I get is a message box... last line of code before End Sub

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    07-08-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Using a macro to select rows with a specific word in a column

    Wow, you're right - something resolved it. Well, thanks again - You've all been great!

  18. #18
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using a macro to select rows with a specific word in a column

    ahilty,

    I do not get a prompt concerning column headers for sorting...?

    [EDIT]: My post took a long time to actually post. Glad you got it sorted
    Last edited by tigeravatar; 07-15-2013 at 04:25 PM. Reason: added edit

+ 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. Copy rows containing in column A specific word
    By Odeen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2013, 10:24 AM
  2. [SOLVED] Macro to delete rows that DO NOT contain specific word
    By enfieldsteve in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-19-2013, 10:59 AM
  3. [SOLVED] Need a macro to delete rows with specific word
    By renjithvakkayil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-07-2013, 12:54 PM
  4. Macro to look for a specific word and delete rows
    By excelaspire0219 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-27-2009, 06:35 PM
  5. Replies: 1
    Last Post: 07-04-2006, 07:00 PM

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