The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

Regina,
Try this

Sub clear_part2()
Dim r As Range
On Error Resume Next
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not r Is Nothing Then
r.Clear
End If
End Sub


"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

Hi Alok,
I copied your reply directly into the macro and it didn't do any actions.
It didn't error but there wasn't any action either. Thanks for you help!
Any other suggestions?
--
Regina


"Alok" wrote:

Regina,
Try this

Sub clear_part2()
Dim r As Range
On Error Resume Next
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not r Is Nothing Then
r.Clear
End If
End Sub


"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

Have you got any cells with constant values? Alok's code tested the range
first for matches, and does nothing if there are none.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Regina" wrote in message
...
Hi Alok,
I copied your reply directly into the macro and it didn't do any actions.
It didn't error but there wasn't any action either. Thanks for you help!
Any other suggestions?
--
Regina


"Alok" wrote:

Regina,
Try this

Sub clear_part2()
Dim r As Range
On Error Resume Next
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not r Is Nothing Then
r.Clear
End If
End Sub


"Regina" wrote:

The subject line is the error message I get when trying to run the
following
code. When I hit debug, it highlights the line that starts with "set
r -
range...". What I am trying to do is clear the constants but leave
the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

Regina,
The best method to see why nothing is happening is to debug the code by
walking through it.
Click on the line Set r = ..
Press F9
This will set a break point on the line.
Run the macro by pressing F5.
The yellow line will appear and will overlap the break point.
Now step through the code by pressing F8
If your code skips over the line
r.Clear
then it means the code did not find any cells containing constants.
If it goes into that line (that is the line r.Clear becomes yellow) then you
can check the address of the cells which the code found to be the cells
containing the constants by typeing ? r.address in the immediate window. (If
the immediate window is not in view type Control-G to view it)

Hope this helps.






"Regina" wrote:

Hi Alok,
I copied your reply directly into the macro and it didn't do any actions.
It didn't error but there wasn't any action either. Thanks for you help!
Any other suggestions?
--
Regina


"Alok" wrote:

Regina,
Try this

Sub clear_part2()
Dim r As Range
On Error Resume Next
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not r Is Nothing Then
r.Clear
End If
End Sub


"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

Regina,

Also please note that your code currently works on the active sheet.


"Regina" wrote:

Hi Alok,
I copied your reply directly into the macro and it didn't do any actions.
It didn't error but there wasn't any action either. Thanks for you help!
Any other suggestions?
--
Regina


"Alok" wrote:

Regina,
Try this

Sub clear_part2()
Dim r As Range
On Error Resume Next
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not r Is Nothing Then
r.Clear
End If
End Sub


"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

Maybe you want to use just xlConstants instead of xlCellTypeConstants.

"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

Hi JLGWhiz,
There is no Enumerated value of xlConstants in the XlCellType enumeration.
Hence that is unlikely to be the problem.
Alok

"JLGWhiz" wrote:

Maybe you want to use just xlConstants instead of xlCellTypeConstants.

"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

From the immediate window in xl2003's VBE:

?xlConstants
2

?xlcelltypeConstants
2

But I don't think that's the problem, either.

Alok wrote:

Hi JLGWhiz,
There is no Enumerated value of xlConstants in the XlCellType enumeration.
Hence that is unlikely to be the problem.
Alok

"JLGWhiz" wrote:

Maybe you want to use just xlConstants instead of xlCellTypeConstants.

"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

--

Dave Peterson

I agree with you Dave that in this case both constants happen to have the
same value but using the other defeats the purpose of using incorrect
constant defeats the purpose and makes the code even more difficult to
maintain than using say the value of 2.

"Dave Peterson" wrote:

From the immediate window in xl2003's VBE:

?xlConstants
2

?xlcelltypeConstants
2

But I don't think that's the problem, either.

Alok wrote:

Hi JLGWhiz,
There is no Enumerated value of xlConstants in the XlCellType enumeration.
Hence that is unlikely to be the problem.
Alok

"JLGWhiz" wrote:

Maybe you want to use just xlConstants instead of xlCellTypeConstants.

"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

--

Dave Peterson

I think xlconstants was the original constant. xlcelltypeconstants came along
in xl97 (IIRC). But for compatibility, MS had to keep the old one alive. So I
don't think I'd characterize using xlconstants as being incorrect (although I
favor the newer versions, too).



Alok wrote:

I agree with you Dave that in this case both constants happen to have the
same value but using the other defeats the purpose of using incorrect
constant defeats the purpose and makes the code even more difficult to
maintain than using say the value of 2.

"Dave Peterson" wrote:

From the immediate window in xl2003's VBE:

?xlConstants
2

?xlcelltypeConstants
2

But I don't think that's the problem, either.

Alok wrote:

Hi JLGWhiz,
There is no Enumerated value of xlConstants in the XlCellType enumeration.
Hence that is unlikely to be the problem.
Alok

"JLGWhiz" wrote:

Maybe you want to use just xlConstants instead of xlCellTypeConstants.

"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

--

Dave Peterson


--

Dave Peterson

None of this is working. I am not very good in VB so please bare with me. I
think some of my terminology was wrong in the first question that I asked,
therefore I am sure that is why this is not doing what I want. I said I
wanted to clear the constants and leave the formulas. What I am trying to do
is clear data that is in a linked cell from a worksheet within the same
workbook and retain the links. Thanks,
--
Regina


"Regina" wrote:

The subject line is the error message I get when trying to run the following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina

You can't, you have to clear the data in the source cell(s).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Regina" wrote in message
...
None of this is working. I am not very good in VB so please bare with me.
I
think some of my terminology was wrong in the first question that I asked,
therefore I am sure that is why this is not doing what I want. I said I
wanted to clear the constants and leave the formulas. What I am trying to
do
is clear data that is in a linked cell from a worksheet within the same
workbook and retain the links. Thanks,
--
Regina


"Regina" wrote:

The subject line is the error message I get when trying to run the
following
code. When I hit debug, it highlights the line that starts with "set r -
range...". What I am trying to do is clear the constants but leave the
formulas in the "D7:E125" Range. Help!!!!

' Sub clear_part2()
Dim r As Range
Set r = Range("D7:E125").SpecialCells(xlCellTypeConstants)
r.Clear

End Sub
--
Regina