Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

migrated from stackoverflow.com Apr 16 '11 at 13:01

This question came from our site for professional and enthusiast programmers.

    
Are you looking for a solution that uses VBA? This is borderline off-topic. –  Matt Ball Apr 14 '11 at 14:04
    
@Matt: No I said in the text box to use a formula, it's not borderline off topic? –  Mike Apr 14 '11 at 14:08
    

4 Answers 4

up vote 1 down vote accepted

Just to add on to what ToonMariner said, that website provides both a formula and a VBA solution. I found the author's details on using the formula a bit confusing. Using the provided formula:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

  1. Simply highlight one column by clicking the header and name it 'BlanksRange'

  2. The 'BlanksRange' column should hold your values. (Column A from your screenshot)

  3. Highlight a second column and name it 'NoBlanksRange'.

  4. Click the first cell in 'NoBlanksRange', paste the formula into the formula textbox at the top, and press CTRL-SHIFT-ENTER at the same time

  5. With the first cell still highlighted, click the bottom right corner of that first cell and drag this down to the same number of rows as the 'BlanksRange' column.

  6. Wait a few seconds (depending on the data) and your desired output will be revealed.

Make sure you wait a few seconds to see the results... on my machine, even only 10 rows or so required about 4 seconds before the correct data showed up.

Also make sure when you copy and paste the formula from a website that the whole thing gets in there because the linebreaks may cause it to only paste a portion of the formula.

share|improve this answer

If you select the column and press F5, click Special, choose Blanks then right click the selected cells and delete - that'll get rid of the empty cells.

Screenshot

Not my bag but I also found this.

share|improve this answer

Could you sort the data in its existing location?

share|improve this answer

You can also use AutoFilter, to filter the list getting rid of the blanks, then copy-and-paste it where you want.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.