how to remove empty rows in excel for mac

If you only have a single cell selected, the macro finds all blank rows in the UsedRange of the active sheet. The following macro will select all the entire blank rows in the selected range.

This allows us to perform the entire task with a click of a button. The fastest approach to deleting blank rows is to use a macro. #3 – Use a Macro to Select or Delete Entire Blank Rows You can turn off the Table Formulas (structured references) if you don't like them. I use an Excel Table ( Beginner's Guide to Tables) in this example, and highly recommend using Tables.

Delete the rows with the Ctrl+ - keyboard shortcut.

Select all visible rows in the filter range.

Add a column with the COUNTA formula to count non-blank cells.

To recap, the steps to delete entire blank rows are: Next we just select and delete the visible rows. This will show all of the entire blank rows, and hide all non-blank rows. We then filter the new column for 0 (zero). If the formula returns a 0 (zero) then all the cells in that row are blank. This will give us a column of values that returns the number of empty or non-blank cells in the row. We can add this formula to the right of the table, and copy down to each row in the table. The COUNTA function returns the count of cells that are NOT empty in a range. #2 – Use a Formula to Identify Entire Blank RowsĪnother approach is to use a formula with the COUNTA function. The rows can be deleted by pressing the Delete button on the Home tab of the ribbon (keyboard shortcut: Ctrl+ -)Ĭheckout my 3-part video series on Excel Filters to learn more time saving shortcuts. Once you have a filter applied that makes only the entire blank rows visible, then select and delete the rows. However, this can be time consuming if you have a lot of columns in your data set. You can continue to filter other columns for blanks until all cells in the range or Table are blank. Therefore we do NOT want to delete these rows. We can see that rows 4 & 9 still contain data in the other columns.

In our example file we can filter the Customer column for blanks. If our data set contains a column that ONLY has blank cells in the blank rows, then we can filter for blanks on that column. Let's look at some ways to save time and make this process easy. There are several ways to accomplish this task. If a row contains a few blank cells, we still want to keep that row of data. In this case we want to delete rows that are entirely blank. One very common task we do in Excel is to delete these blank rows from the worksheet, especially if you are preparing your data for use with a pivot table. If you are exporting reports from any type of system then there is a good chance the data contains blank rows.

Delete-Blank-Rows.zip Download 3 Ways to Find & Delete Blank Rows

How to remove empty rows in excel for mac