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

**ahilty** · 07-15-2013, 02:36 PM

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

**LaffyAffy13** · 07-15-2013, 02:58 PM

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.

**tigeravatar** · 07-15-2013, 03:02 PM

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.

**ahilty** · 07-15-2013, 03:07 PM

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.

**ahilty** · 07-15-2013, 03:13 PM

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.

**jhren** · 07-15-2013, 03:17 PM

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.

**tigeravatar** · 07-15-2013, 03:21 PM

ahilty,

The completed code would look like this:

Please Login or Register  to view this content.

**LaffyAffy13** · 07-15-2013, 03:21 PM

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

**ahilty** · 07-15-2013, 03:25 PM

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).

**ahilty** · 07-15-2013, 03:29 PM

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

**jhren** · 07-15-2013, 03:32 PM

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.

**ahilty** · 07-15-2013, 03:38 PM

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.

**jhren** · 07-15-2013, 03:43 PM

Originally Posted by ahilty

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.

**tigeravatar** · 07-15-2013, 03:48 PM

it is as simple as jhren states. Here is the updated code with the insert line:

Please Login or Register  to view this content.

**ahilty** · 07-15-2013, 03:51 PM

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?

**jhren** · 07-15-2013, 04:00 PM

Originally Posted by ahilty

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.

**ahilty** · 07-15-2013, 04:02 PM

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

**tigeravatar** · 07-15-2013, 04:20 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Copy rows containing in column A specific word

[SOLVED] Macro to delete rows that DO NOT contain specific word

[SOLVED] Need a macro to delete rows with specific word

Macro to look for a specific word and delete rows

[SOLVED] select rows to print where there's data in specific column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions