Select Entire Rows of Multiple Selected Cells – Calling VBA + Macros in Action

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.

[jbox color=”red” radius=”0px”]For more of such shortcuts that can help you make selection easily please read Excel Most Wanted – Selection shortcut keys Mega collection[/jbox]

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:

1. find cells that contain selected texts and select them
2. 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.