What's wrong with this code?
 

If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run. The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James

What's wrong with this code?
 

Try this:

If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
End If


"access user" wrote:

If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run. The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James

What's wrong with this code?
 

Try:

On Error Resume Next
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If
On Error GoTo 0

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run.
The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James

What's wrong with this code?
 

Or rather:

On Error Resume Next
If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
End If
On Error GoTo 0

(Got mixed up with JLGWhiz JLGWhiz's code)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run.
The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James

What's wrong with this code?
 

Hi Guys

Thanks to both of you for replying. Sandy you get the cigar. JLGWhiz - that
still gave the same error. Only difference appears to be the 'On Error Resume
Next' method. Don't understand why but thanks.

'G'day cobbers' - is that what they say over there? :-)

"Sandy Mann" wrote:

Try:

On Error Resume Next
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If
On Error GoTo 0

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run.
The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James

What's wrong with this code?
 

Just my luck! I give up smoking then I get offered a cigar! <g Thanks
for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"access user" wrote in message
...
Hi Guys

Thanks to both of you for replying. Sandy you get the cigar. JLGWhiz -
that
still gave the same error. Only difference appears to be the 'On Error
Resume
Next' method. Don't understand why but thanks.

'G'day cobbers' - is that what they say over there? :-)

"Sandy Mann" wrote:

Try:

On Error Resume Next
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If
On Error GoTo 0

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it
run.
The
above is not trapping that condition as the last line before End If
always
runs and brings up the error 'no cells' - I do not want this error to
be
shown, just want the code to terminate in that case.

tia
James

What's wrong with this code?
 

If you're going to use "On error", why not just:

On Error Resume Next
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
On Error GoTo 0

===
But depending on what the selection is, I'd use:

On Error Resume Next
intersect(selection,Selection.SpecialCells(xlCellT ypeConstants)).ClearContents
On Error GoTo 0

it'll avoid any problems if a single cell is selected.

Sandy Mann wrote:

Or rather:

On Error Resume Next
If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
End If
On Error GoTo 0

(Got mixed up with JLGWhiz JLGWhiz's code)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run.
The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James


--

Dave Peterson

What's wrong with this code?
 

Thanks Dave - I'll look into that.
James

"Dave Peterson" wrote:

If you're going to use "On error", why not just:

On Error Resume Next
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
On Error GoTo 0

===
But depending on what the selection is, I'd use:

On Error Resume Next
intersect(selection,Selection.SpecialCells(xlCellT ypeConstants)).ClearContents
On Error GoTo 0

it'll avoid any problems if a single cell is selected.

Sandy Mann wrote:

Or rather:

On Error Resume Next
If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
End If
On Error GoTo 0

(Got mixed up with JLGWhiz JLGWhiz's code)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run.
The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James


--

Dave Peterson

What's wrong with this code?
 

These virtual cigars are quite good for you :-)

"Sandy Mann" wrote:

Just my luck! I give up smoking then I get offered a cigar! <g Thanks
for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"access user" wrote in message
...
Hi Guys

Thanks to both of you for replying. Sandy you get the cigar. JLGWhiz -
that
still gave the same error. Only difference appears to be the 'On Error
Resume
Next' method. Don't understand why but thanks.

'G'day cobbers' - is that what they say over there? :-)

"Sandy Mann" wrote:

Try:

On Error Resume Next
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If
On Error GoTo 0

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it
run.
The
above is not trapping that condition as the last line before End If
always
runs and brings up the error 'no cells' - I do not want this error to
be
shown, just want the code to terminate in that case.

tia
James

What's wrong with this code?
 

This might be what you really wanted.

If Selection.SpecialCells(xlCellTypeConstants).Count < 1 Then
Exit Sub
Else
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
End If

"access user" wrote:

Thanks Dave - I'll look into that.
James

"Dave Peterson" wrote:

If you're going to use "On error", why not just:

On Error Resume Next
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
On Error GoTo 0

===
But depending on what the selection is, I'd use:

On Error Resume Next
intersect(selection,Selection.SpecialCells(xlCellT ypeConstants)).ClearContents
On Error GoTo 0

it'll avoid any problems if a single cell is selected.

Sandy Mann wrote:

Or rather:

On Error Resume Next
If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlCellTypeConstants).ClearC ontents
End If
On Error GoTo 0

(Got mixed up with JLGWhiz JLGWhiz's code)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"access user" wrote in message
...
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlConstants).ClearContents
End If

Basically, if the cells in question (either a selection or a range) are
already cleared then I do not want the sub run, otherwise I want it run.
The
above is not trapping that condition as the last line before End If always
runs and brings up the error 'no cells' - I do not want this error to be
shown, just want the code to terminate in that case.

tia
James


--

Dave Peterson