Removing Blank Cells Resulting From Formula

Hello. This is my first time posting for a VB question. What I am looking to do is create a code that will scan a designated range and remove any blank rows. These blank rows are a result of a formula that I created from an =IF statement leaving a " " if it was false. Is there a way to remove these blank rows and possibly even shift the cells up?

A B C D E
1 ! ! ! ! !
2
3@ @ @ @ @
4# # # # #
5

In this example, numbers were just replaced with random symbols, but what I would like to have happen is have rows 2 and 5 removed and have the data shifted up.

Is this possible? Thank you.

Originally Posted by davegugg

Assuming your list of data is in A1:A20, this would do it:

Please Login or Register  to view this content.

Using this code, I was able to remove any cells that were just empty...It did not take out any cells that were blank resulting from a formula. You can see in the attached file that I have a very basic =IF statement for some cells causing them to return " ". Can these cells/rows be removed as well, or can it only be the ones that are truly blank and empty?

Originally Posted by davegugg

Can you change the formula to make the cells empty:

=If(True=True,"","Otherwise")

I changed the formula around and made the cell blank if the result was true and had it display a value if it was false. Running the code did not remove these cells.

Hey Dave,

I ran the code you gave me, and it didn't seem to remove any of the cells in the list. Is there anything in that code that I need to change to work for the spreadsheet I am using?

Originally Posted by davegugg

Are the blank cells in column A? Are the blank cels = "" or " "?

In the spreadsheet I am testing this with, the blank cells are in column A. The blank cells are the result of the formula with "" and not " ".

Optimally, if possible, I would like to use this to run within a specified range.

I'm not exactly sure how to do this, but would it make sense to have a macro in place to search for cells that returned "" and clear all their contents (hopefully removing the formulas) and then running the other macro you supplied earlier to remove blank wells?

Originally Posted by davegugg

This for a specified range:

Please Login or Register  to view this content.

Works perfectly!! Thank you so much for the help.

I am trying to use one or two words in a cell (column named "Model") and then use that to return the closest matching cell in a lookup table (Column "Activity"). In most cases, only one word in the column "Model" is present in the column "Activity".

I have attached a template sheet the output column with one example of the ideal output.

I appreciate you help.
Thanks in advance!

Hi Davegug
It's rather late but I must thank you for this tip. I had been looking for a similar solution. Thanks a lot.

Originally Posted by davegugg

This for a specified range:

Please Login or Register  to view this content.