How To Get Rid Of Blank Rows in Excel Using "COUNTA" Function

What you doing when your boss hand you a job, perhaps 500 rows or more with blank rows between? He wants all blank rows removed.
You probably have your way of doing it, would like to share a lil. 😃😃😃😃 There are several ways of solving this problem, let me start with the "Counta" function today.

Counta: counts the number of cells in a range that are not empty as seen above.

Last Cell: This is the last cell in the sheet with value or format

Note that I mentioned the F5 key in the last post How Do You Fill These Blank Cells?

Use the F5 key on your PC, click SPECIAL and find "Last Cell"

Now let us solve this! Activate cell H3, input the COUNTA function. Select the range B3:G3 and press enter. {like this..=COUNTA(B3:G3)}

Note, in my case, I adjusted my Last Cell to align with the array. Hold your SHIFT KEY + ARROW KEY to move left or right with selection.

Back to work! With Range H3 activated, hold the shift key to jump to the last cell (recall to find the last cell as defined above starting with the F5 key or the "Find & Select" button in the extreme right of your Home Ribbon)☺️☺️☺️

Press F2 and CTRL ENTER to populate down

Select B2:H2 and press CTRL+SHIFT+L  to add FILTER

In cell H2, select the drop-down button to check "0"

Delete the blank rows and boom, you beat it!

Clear the FILTER as shown below

Your work is done!

This is what it should look like..

You can remove the helper-column (as seen in the image above, no column H)