Delete Blanks from Formulas using Visual Basic

twolf

New Member
Joined
Dec 30, 2005
Messages
2
Hi,

I a novice. I am trying to use Visual Basic to delete blanks that are left when I use a formula. This is what I do:

1) Write an IF statement. If the answer is false, then I ask it to return "" (nothing).

2) I copy over the cells with the formula by pasting values.

3) The cells that had "" are still do not act blank. How do I remove all contents from those cells?

Thanks,
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Welcome to MrExcel!

Here's what I'd suggest (works for me):
Change the FALSE part of your IF statement to return something that wouldn't be returned for TRUE. For example, I use "zzz". Copy and paste your data. Then Find & Replace. Find "zzz" and leave the replace section blank.

Regards,
 

twolf

New Member
Joined
Dec 30, 2005
Messages
2
Barrie,

Thank you very much for your reply. That is the same strategy I planned on using, but I thought that there might be an easier way using a macro. Either way, the "zzz" approach will work well enough for my needs.

Thanks again,
 

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,759
Members
412,482
Latest member
arooshrana2
Top