How to write a Clearcontents command in a macro
 

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

How to write a Clearcontents command in a macro
 

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

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

Can you help ?

"Arno" wrote:

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

How to write a Clearcontents command in a macro
 

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

Arno wrote:

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

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

Can you help ?

"Arno" wrote:

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


--

Dave Peterson

How to write a Clearcontents command in a macro
 

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" wrote in message
...
Try this:
Worksheets("Sheet1").Range("U1:AA5000").ClearConte nts
or
Worksheets("Sheet1").Range("U1:AA5000").Value = ""

Arno wrote:

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

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

Can you help ?

"Arno" wrote:

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


--

Dave Peterson

How to write a Clearcontents command in a macro
 

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.

Skinman wrote:

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" wrote in message
...
Try this:
Worksheets("Sheet1").Range("U1:AA5000").ClearConte nts
or
Worksheets("Sheet1").Range("U1:AA5000").Value = ""

Arno wrote:

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

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

Can you help ?

"Arno" wrote:

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


--

Dave Peterson

--

Dave Peterson