Delete empty rows

[EcoWill]

Hi T.T. - Each month receive at my desk a series of new spreadsheets, each with thousands of rows of data that I have to reorganize to create pivot tables. I do not have the authority to change the formatting at their source (they come from government agencies and that is HOW THEY DO IT) The first thing I have to do is go through every one of these and DELETE EVERY OTHER ROW, because practically every other row is blank. Empty. Why? (government. it is how they do it!) Can I instead do something simpler to get rid of the blank rows? A formula, or something? It would sure be nice. It can take hours. THANKS!

 

[SkipVought]

Hi,

Select all the data by selecting the COLUMNS common to the table

Turn on the AutoFilter.

Filter any column to select BLANKS

Select the ROWS containing BLANKS, right-click and DELETE.

Clear the filter criteria.

If you want, you can record a macro that does these steps and store it in your PERSONAL.XLSB workbook, in order to run automatically to condition your workbooks. If you care to do that, post back with your recorded code for help generalizing & customizing your macro.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

Of course. Perfect. Thanks.

 

[EcoWill]

When i clear the filter, the blanks reappear!

 

[SkipVought]

Then you did not follow my instructions, by SELECTING the BLANK ROWS and [highlight]right-click and DELETE[/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

You are right. Not only did I not follow your instructions, but I also did not understand them. When I turned the filter on, I unchecked []Blanks and the blank rows disappeared. So far so good. Now what am i supposed to right-click on? What blank rows? They have vanished.

 

[SkipVought]

I unchecked []Blanks

WHY? That is the OPPOSITE of what you were instructed to do.

Select BLANKS in the AutoFilter!!!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

I have no idea what you are talking about. When I selected the filter and checked blanks only, practically everything on the page disappeared. I am not an Excel expert. You are. Not me. And when this government job ends, I will retire and travel the country in my new expensive RV. I am only trying to understand Skip.

 

[SkipVought]

When you UNCHECK a value, guess what! Those rows containing that value are no longer displayed in the table.

So...
[tt]
1. UNCHECK Select All

2. CHECK BLANKS

3. VOLA, y'all!
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

So then what I did was to just copy and paste just the values to another new worksheet and left the formatting behind. Internally it was remembering the blanks. Anyway, I have moved on with the workaround and I appreciate the help.

 

[SkipVought]

This is not rocket science!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

If it were any kind of science I would be better at it. I am a biochemist doing Excel!

 

[lionelhill]

another way to get rid of blank rows is just to sort the entire thing (so all the blanks end up in a contiguous block).

 

[papadba]

But that could change the order/sequence of the "good" rows. . .

Might be ok, might not. . .

 

[SkipVought]

In that case, you add a helper column of sequential numbers to sort it back into original order.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

Ah yes - the helper column of numbers. Jeez. I have done that often in the past, but it has been a while. Totally forgot. Anyway - this was all good advice, and now I have a plan going forward. Thanks and hope you are having a nice holiday weekend.