Abstract
Transcript
In this video, we'll look at a more complicated example of a shortcut recipe to remove blank rows. In this case, some rows are partially blank, so we need to add a formula in a helper column to test and make sure that the entire row is blank before deleting.
This worksheet contains measurement data. All together, the table has more than 1000 rows, and each row has 15 measurements, using codes A through F.
In this case, I also want to remove blank rows, but it's much harder to see which rows are really blank. I can't just select all blanks in a column, because adjacent values in the same row may not be blank.
One way to handle this is to add a helper column with a formula that does the check for me.
For the formula, I'm going to use the amazing SUMPRODUCT function, together with the LEN function to check the length of each cell in a row.
Since all cells are either blank or contain just one letter, this formula returns a count of cells that are not blank.
Notice that I can't just double click the fill handle to send the formula down, because column P contains blanks. And, if I try to extend the selection, I'll end up at the bottom of the worksheet.
Here's a nice workaround, when you have this problem. It's actually useful shortcut recipe itself.
Move to the bottom of the worksheet with control down, and the step one column left, then move back up using control up arrow. This gets you near the bottom of the data.
Now, move right and extend the selection up. Then you can move the active cell with control period, and enter the formula with control enter.
Ok, so now I have a count of all non-blank cells. Now, I could filter the list for zero values, and then delete. But I can also use Go To Special. I just need to tweak the formula a bit to return TRUE when all cells are bank, and nothing if not.
For that, I'll just use the IF function.
Now the formula returns TRUE only when all cells in all columns are blank.
I can use Go To Special again, this time selecting formulas that return logicals. Finally, with all TRUE values selected, I can delete entire rows as before.
Using the same basic recipe, you can remove rows that meet almost any condition.
