enter image description here

  • 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

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:


  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.


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.


Not my bag but I also found this.


Could you sort the data in its existing location?


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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