How to write a Clearcontents command in a macro

[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.
I have been told that probably since I 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

My question is: how can I write the instruction above in the macro to make
it work?


Thank you for your help ! Arno

 

[Arno]

Hi, I tried the following but I get a sintax error

Worksheets("Sheet1").Range("U1:AA5000").Cell.Value="".ClearContents

Can you help ?

 

[Dave Peterson]

Try this:
Worksheets("Sheet1").Range("U1:AA5000").ClearContents
or
Worksheets("Sheet1").Range("U1:AA5000").Value = ""

 

[Skinman]

Maybe this is what you need
If some cells have values and you only want to delete blank cells

Set myrange = Range("U1:AA5000")
For Each c In myrange
If c.Value = "" Then c.Delete Shift:=xlToLeft
Next

Arno,
If Cell. Value = "" ----You can't ClearContents because it has no contents
to clear
Skinman

 

[Dave Peterson]

You can do this:

activesheet.range("a1").clearcontents
activesheet.range("a1").clearcontents
activesheet.range("a1").clearcontents
activesheet.range("a1").clearcontents
activesheet.range("a1").clearcontents

All 5 lines will run ok. If the cell is empty, then you can still use
..clearcontents against it.