Problems to delete blank/empty cells

[Arno]

Hello,

I copied data from an excel worksheet; all this data contains IF formulas.

Then in another column I paste special, as value, and I select the area in
order to delete the blank cells (Go to Special, blank, delete)

Unfortunately the application always says: "no cells found" and I cannot
delete the empty cells in the selection.
Probably I am pasting a string and Not an empty cell.

But I do not know how to solve this.

Thank you for your help ! Arno

 

[Gary''s Student]

Edit > Goto > Special > Blanks
will only select truly empty cells. It will not select cells with a formula
returning blanks or any paste of these cells.

 

[JLGWhiz]

You could do a For ... Each loop and check for cell.Value = "" then delete.

 

[Orion Cochrane]

I agree with JLGWhiz. Since you have IF functions which may return a blank
value ("") if a condition is/isn't met, the better route for deleting would
be:
For ... Each loop check for Cell.Value="" then ClearContents
Using ClearContents preserves formatting of cells and clears the contents of
the cell (the IF formulas in your case) that returns a "" value.

 

[Arno]

Hi,
thanks for your reply but how do I exactly erite in the macro:For.. Each
loop check for Cell.Value="" then ClearContents ?

 

[Gary''s Student]

Here is some code:

Sub clear_blank()
For Each r In Selection
If r.Value = "" Then
r.ClearContents
End If
Next
End Sub


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. Select the cells to be “cleanedâ€
2. ALT-F8
3. Select the macro
4. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm