This question came up in my Miami Power Excel seminar. Every day, a report is downloaded to Excel with tiny sections, each separated by a blank row. We need a quick way to remove all the blank rows, but the data should not be sorted. Thanks to some great shortcut keys from Smerling, the problem becomes simpler.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1932.
Delete Blank Rows Without Sorting.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Someone on my Miami Seminar had a data set he gets every day, like this: with the blank rows.
The question is: how to get rid of the blank rows?
Well, you know, hey, the fast way to get rid of the blank rows is to sort the data, but we needed to keep the sequence.
And someone named Smerling, right in the front row of the Seminar, gave me this great set of steps.
I love this, all ready?
Ctrl+End goes to the last cell with data, Ctrl+Shift+Home then selects the whole data set.
Ctrl+Shift+L turns on the Filters.
And then it's just a simple matter of going to one of the Filters, uncheck (Select All), choose the (Blanks), click OK and then select those rows and we will Delete, Delete Sheet Rows.
Turn off the Filters, which Ctrl+Shift+L will do.
And you've gotten rid of the blanks.
And that is a good way.
The way that I typically would do this, is just sort the whole data set.
But if you need to keep things in a certain sequence, then we have to go out here and add the sequence field, which you can quickly do with =ROW(), Ctrl+C, convert to values and then sort by one of these columns.
A-to-Z, because I have added the sequence column, it considers all the other sections as part of that data set.
Delete these, I'll E, D, R. And then finally sort again by sequence and delete.
And you have to admit, those other steps, Ctrl+End, Ctrl+Shift+Home, Ctrl+Shift+L, are probably faster than adding the whole sequence field with the same results.
Okay, I want to thank for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1932.
Delete Blank Rows Without Sorting.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Someone on my Miami Seminar had a data set he gets every day, like this: with the blank rows.
The question is: how to get rid of the blank rows?
Well, you know, hey, the fast way to get rid of the blank rows is to sort the data, but we needed to keep the sequence.
And someone named Smerling, right in the front row of the Seminar, gave me this great set of steps.
I love this, all ready?
Ctrl+End goes to the last cell with data, Ctrl+Shift+Home then selects the whole data set.
Ctrl+Shift+L turns on the Filters.
And then it's just a simple matter of going to one of the Filters, uncheck (Select All), choose the (Blanks), click OK and then select those rows and we will Delete, Delete Sheet Rows.
Turn off the Filters, which Ctrl+Shift+L will do.
And you've gotten rid of the blanks.
And that is a good way.
The way that I typically would do this, is just sort the whole data set.
But if you need to keep things in a certain sequence, then we have to go out here and add the sequence field, which you can quickly do with =ROW(), Ctrl+C, convert to values and then sort by one of these columns.
A-to-Z, because I have added the sequence column, it considers all the other sections as part of that data set.
Delete these, I'll E, D, R. And then finally sort again by sequence and delete.
And you have to admit, those other steps, Ctrl+End, Ctrl+Shift+Home, Ctrl+Shift+L, are probably faster than adding the whole sequence field with the same results.
Okay, I want to thank for stopping by, we'll see you next time for another netcast – MrExcel.
