Article updated on April 10, 2018

I highly recommend you keep the original data and only copy the data excluding blank rows and paste to a new worksheet.

Let me explain why, if you by accident delete rows partially empty and don't notice it or use a macro that you can't undo, then that data is gone.

I am going to use the Advanced Filter to hide blank rows, in order to do that I need to add a criteria range.

The criteria range consists of the table header names and the criteria below the header names, see cell range B2:D5 in the picture above.

Make sure the criteria header names are identical to the table header names.

The criteria are <> meaning not equal to nothing. They are on a row each so at least one condition must be true (OR logic) to filter a row.

It is now time to start the Advanced Filter, go to tab "Data" on the ribbon. Click on the "Advanced" button.

The "Advanced Filter" dialog box appears.

Select the criteria range and the list range, then click on OK button.

Select and copy cell range B7:D17 and paste to a new worksheet.

If you paste the data next to your original table remember to clear the filter so you can see all the rows.