Can Excel "slide up" rows with content thru empty rows to condense

[Guest]

I am drawing 120 randomly ordered rows of data from a summary worksheet to
separate worksheets. The data consists of a Name, a Category, and a number,
e.g., "John" , "apples" and 45. There are four related worksheets, each
assigned to collect a different "fruit". I know how to draw John 45 to the
"apples" worksheet, but want to know if Excel can automatically "condense
upwards" the rows of data on the destination sheets to eliminate empty rows.
I know I can Sort to accomplish this, but want to eliminate that step.
Filemaker Pro can "slide up" data within a field for printing purposes; can
Excel slide up data through empty rows?

 

[Myrna Larson]

Not automatically. You would have to hide the empty rows manually or use a
macro. For the formula, you could perhaps use Data/AutoFilter on one of the
columns and select Non-blanks.

 

[Gord Dibben]

Automatic generally means VBA and in this case probably some sort of event
code.

You can do it manually quite quickly.

Select the destination sheets by SHIFT + Click or CTRL + Click if
non-contiguous.

Select the columns with data.

F5>Special>Blanks>OK

Edit>Delete>Entire Row or Shift cells up.

DO NOT FORGET to ungroup the sheets when completed.


Gord Dibben Excel MVP