I never imagined that my first article involving VBA will be this short. And I kept myself from diving into VBA and Macros until now so that I stay basic and all my dear readers and followers get to know the basics first. But this time I had to call the real army in! So follow along.
Selecting an entire row where active cell is already very easy. For example if your active cell is C8 and you want to select whole Row 8 then simply hit Shift+Space bar and this will select the whole row where your active cell is. Similarly if you want to select the whole column of active cell, your shortcut is Ctrl+Space bar.
But one of our member who recently joined our facebook community asked few questions and one was “how to select entire rows that contain specific text”
Now this seemingly one questions have two edges:
- find cells that contain selected texts and select them
- select all rows of the cells selected in 1
Interesting and intimidating. And this very query left me no choice but to jump in VBA world. For those who don’t know about VBA it is a short for Visual Basic for Applications and in our case application is Excel. In short, Visual Basic for Excel. Visual Basic is a programming language and if you couple the power of Excel with programming, then what Excel can do is probably limited only up to your imagination. You already know how powerful Excel is. Believe me without VBA, Excel is without a heart!
Back to our task. So first we want to find all the cells that contain the specific text we want. This could result in multiple cells getting selected and then select whole rows of the selected cells.
Finding and selecting cells with specific text
This part is easy. And I have discussed how to do this in many of my articles before. But here it is again:
Step 1: Select the range if you want to search only in selected area. If you want to search whole worksheet then proceed to step 2.
Step 2: Go to Home tab > Editing tools group > click Find & Select drop down > click Find. Or simply hit Ctrl+F shortcut.
Step 3: In the find what field type the text you want to find. If it is a word then put a whole word. If you have part of the word then you can use wildcards like asterisk (*) or question marks (?).
Step 4: Hit Find all button. This will search for the criteria provided and display the results in the same Find dialogue box.
Step 5: To select all of the cells that contain the searched word. Click any result and hit Ctrl+A. This will not only select all results but also the cells that contain that search string. Click close. Notice that cells will stay selected. And this is exactly what we need for our next step
Select entire rows of all the selected (multiple) cells
Step 1: Hit shortcut Alt+F8. This will bring up macro dialogue.
Step 2: In the name field type: selectentirerow. Make sure there are no spaces. And hit Create button.
Step 3: The moment you hit create button you will enter the strange world of VBA. Yes! This is the playground of all Excel gurus. Welcome aboard. You will get an active cursor inside a window. Active cursor will be below the line that says: Sub selectentirerow()
Step 4: Simply type this:
selection.entirerow.select
Step 5: While you are still inside VB environment, hit F5 key on the keyboard. Nothing happened??? Read next step 😉
Step 6: Come back to your original Excel window where your worksheet is. And you will see that now all the rows where you had selected cells are now selected.
Give me Hi 5! You have just excelled at VBA and one of its function 🙂
Bonus Tip – Assign shortcut key to Macro
Now that you have created a macro, you can use it again and again. To do this you can hit Alt+F8 to bring macro dialogue box, select the macro and hit Run button.
But it is much easier if you assign a shortcut key to this. To do this follow these steps:
Step 1: Hit Alt+F8 to invoke Macro dialogue box. Select the macro you created (named selectentirerow) and click Options button.
Step 2: A new dialogue box will appear. In this you can assign a key that will work in combination with Ctrl key on the keyboard. Type any letter but make sure it is not already used by Excel default features. For example if you type C, it will replace your copy shortcut. So use the key which is vacant. For example Q. Click OK. Your shortcut is now Ctrl+Q
Now you can select entire rows of selected cells easily just by hitting Ctrl+Q shortcut.
Exactly what I was after. Thanks.
this is magic for me too!!!! gosh!!!! thank you very muchhhhhh
Perfect! I was a joy reading your instruction. It was like I was doing magic!
Lots of love,
Hassan
Can’t copy or cut the selection afterwards, very fustrating
“copy command can’t be used on multiple selections”
Incredible. I had a add-on for last years taxes doing this for me in Excel, couldn’t find it again. I knew if I didn’t find a solution on this I would most likely spend a week sorting through data. You saved me for doing a lot of work. I’m very grateful and hope all is well.
Thank you so much!
How do I Ctrl+A across multiple tabs?
First select the tabs by holding CTRL down. This will group the worksheets and then you can execute CTRL+A on group worksheets.
You are a superstar.
You have saved a lot of time.
Thank you.
Here is a question. I am trying these steps, but the Ctrl – A within the Find Dialog box is not working at all for me. I walked thru all the steps, but only get one row at a time, not all. Any ideas? I see the Ctrl A works other places, just not after a Find All.
This is strange. Just checked again and its working fine for me. Just to make sure just left click once on first result in the found list and then hit CTRL+A.
Great, but after all the rows have been selected, I’d like to shade them yellow.
What additional step(s) are necessary to do this?
Once selected you can format it whatever way you wish. From the home tab you can change color of the row easily
Thanks.
But is there another step I can add to the macro to do that automatically?
Once your rows are selected, create a new macro with a relevant name, apply the formatting you want, and stop the macro recorder.
In the VBA Manager (Alt+F11) you’ll see the new code. It will look something like this:
Sub Macro8()
‘
‘ Macro8 Macro
‘
‘
Selection.Style = “Note”
End Sub
Copy the code line and paste it in the selectentirerow() macro. This will now look like this:
Sub selectentirerow()
Selection.EntireRow.Select
Selection.Style = “Note”
End Sub
Now Find and select your cells with specific text and run the selectentirerow() macro.
Love it! This was exactly what I was looking for!
very very useful thank you so much!
You’re a legend! Thank you so much!! 😀 Ctrl+L is my new friend
Years later and this is still helping people! I wish you could’ve seen my face when I went back to my original excel document and all the rows were selected!!!! THANKS!!!
Very useful – running into it telling me that the copy command can’t be used on multiple selections though. What am I missing?
Very Very useful for me…
Your selections all need to be in the same row or column. You should also be able to copy entire rows or columns.