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.
Related articles
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]
Table of Contents How to filter using OR logic between columns Advanced custom date filter 1. How to filter using […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
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.
