Compress Column

[phillyfilly]

When you have a column of information that contains blank cells, how do you compress the column to show the data only without the blank cells, without sorting? Thanks.

[Moo the Dog]

You could filter the column...

For instance, if your data is in column A, select column A then click:
Data tab >> Filter >>
Then click on the down-arrow at the right side of cell A1, remove the check next to (Blanks), and click OK.

- Moo

[phillyfilly]

Moo,

This column contains write-in answers to a survey. Column height is 480 rows. If I used Filter, I would have to scroll down through about 400 unique answers before I got to Blanks. Is there another way? Thanks.

[Moo the Dog]

I'm really confused here... you said you wanted to compress the list to only show cells with data in them.

By filtering the column with the answers and hiding the rows with blanks [by unchecking the (Blanks) checkbox], wouldn't that be exactly what you are trying to accomplish? It certainly sounds like it would compress the list to show only the rows with data in that column.

Do you want access to the blanks first? What exactly do you want as a result?

- Moo

[phillyfilly]

Moo,

I'm sorry to confuse you. Filtering seems like what I will have to do, unless someone else comes up with another solution. What I meant was, when you turn filtering on, and I go to the column I need and click the down arrow, I must scroll down through all the unique answers (about 300) to get to the box to turn off blanks.

No, I don't want the blanks first. What I want to do is what I originally stated in the first post. Thanks for your help.

[:) Sixthsense :)]

While you are in the Autofilter checkbox selection area just press End button from your keyboard which will take you to the end value of the filtered unique values (Not needed to use the Autofilter Scroll Bar to Move to the end).

[Moo the Dog]

@ Philly: Nowwwww I get it... you didn't want to have to scroll down through all of the answers in the Filter selection box... sorry about that.

@Sixth: Thanks. I was skipping over the process and only thinking about the end results. Appreciate the assistance.

- Moo

[phillyfilly]

Sixthsense,

I'm pressing the End button and it's not working. I must be doing something wrong, because I have to scroll down to the Blanks checkbox. Thanks, any way.

[:) Sixthsense :)]

Select the data and apply sort by pressing Alt+D+F+F to enable the filter. then move to the cursor column heading and Use Alt+Down Arrow to expand the Autofilter Option then press Tab to move and use Tab until you reach the CheckBoxes then press End button to move to the end of the checkboxes.