What's wrong with this code?

[access user]

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

 

[JLGWhiz]

Try this:

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Or rather:

On Error Resume Next
If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then
Exit Sub
Else
Selection.SpecialCells(xlCellTypeConstants).ClearContents
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[access user]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

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

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

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

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

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

 

[access user]

Thanks Dave - I'll look into that.
James

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

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

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

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

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

 

[access user]

These virtual cigars are quite good for you

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[JLGWhiz]

This might be what you really wanted.

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