How to delete empty rows
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. Press with mouse on the "Advanced" button.
The "Advanced Filter" dialog box appears.
Select the criteria range and the list range, then press with left mouse button 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.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Create a unique distinct list using Advanced Filter in a macro [VBA]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
Extract unique distinct values if value contains string
The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]
Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]
Lookup with any number of criteria
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
How to filter using OR logic between columns [Advanced Filter]
The built-in filter feature in Excel is a powerful tool, however, it won't allow you to filter with OR logic […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
