Reduce a sparsely populated range in Excel

Question

Is there a way of using Excel's built in functions to remove all blank cells in a range?

A quick a dirty way would be to sort the range, so that the empty cells ends up last, but if I don't want to change the order of the element that is not a good way of doing it.

I know I can do this in VBA, and already has, but I'm more interested if this functionality is avaliable from within Excel.

Example:

A

C
D


F

E

should end up with

A
C
D
F
E

Answer 1

Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go to Edit>Delete.

Edit:

No edit menu in Excel 2007? I must be showing my age. Thanks, Jason Z!

Answer 2

Put your sparsely populated range in Column C, stating at cell C2.

In B2:Bn put the following formula:

=IF(ISBLANK(C2),"",MAX($B$1:B1)+1)

This will number the populated cells, 1 to x (where x is the number of populated cells)

In A2:An put the following formula:

=IF(ISERROR(MATCH(ROW()-1,B:B,0)),"",VLOOKUP(ROW()-1,B:C,2,0))

This performs a lookup on the rownumber of Cell An-1 and brings the value across.

As the number of populated cells (x) is less than the number of cells in the range (n), you need to have the IF(ISERROR(MATCH(ROW()-1,B:B,0)),"" to avoid cells Ax+1:An being filled with "#NA"

Answer 3

The only way I have done this before is to insert another 'key' column (numbers 1 - n) alongside the sparse range. This allows you to reorder the data in its original sequence after you have sorted out the blanks.

Answer 4

While I agree with Richard, there is no Edit menu in Excel 2007. You need to be in the Home tab on the ribbon and click the Find & Select icon. Select Go To Special, check Blanks, and click OK. You can now delete the selected cells.

Answer 5

You can also use the data filter advanced filter and filter in place the unique items. You will be left with one blank cell however as that is a unique result for that cell value.

Answer 6

1. Make another column and fill it with ordered numbers (1,2,3 ...)

2. Then make one more column with formula:

   =IF(A1="";B1;"")

Where A1 is column from your example and B1 - added colunm with ordered numbers

Last. Sort data by column with formula

Answer 7

Highlight the range. Use Goto (F5) Special Constants. Edit Copy . Click in an unused area of the spreadsheet. Edit Paste. Now, if desired, copy the result back over the origin.

Answer 8

context:

• Excel 2003
• know the filter mode

pre :

1. insert a row at the top (maybe not necessary)
2. select the range you want
3. "put" a filter on it

now two ideas:

a) Non Destructive : in the column containing A B C D F E, tell excel to show the 'Non Empty' cells so they won't be displayed

b) Destructive : if you want to delete those lines definitively, choose 'Empty', the table will look empty. Select lines then delete them. Deactivate the filtering and you should end up with your data in order.

Hope that helps