Hi all,

Im using the code below to delete a range. What i would also like i
once the range has been deleted, if the user clicks the buttin again
an error message 'you have already deleted this range
appears....rather than a debug message.

How would i do this??

Cheers all!!!!

Private Sub CommandButton5_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet1")
Set rng = wsquote.Range("knife2")
x = rng.Rows.Count
wsquote.Range("knife2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Su

--
Message posted from http://www.ExcelForum.com

Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet1")
On Error Resume Next
Set rng = wsquote.Range("knife2")
If Err.Number = 0 Then
x = rng.Rows.Count
wsquote.Range("knife2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"gavmer " wrote in message
...
Hi all,

Im using the code below to delete a range. What i would also like is
once the range has been deleted, if the user clicks the buttin again,
an error message 'you have already deleted this range'
appears....rather than a debug message.

How would i do this??

Cheers all!!!!

Private Sub CommandButton5_Click()
Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet1")
Set rng = wsquote.Range("knife2")
x = rng.Rows.Count
wsquote.Range("knife2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
End Sub


---
Message posted from http://www.ExcelForum.com/

Hi Bob,

Good to hear from you. I tried your code but no error message wa
presented. How do i get the error message 'you have already deleted th
range' to appear if the range is no longer.

??????

Cheers!!!!

--
Message posted from http://www.ExcelForum.com

Dim rng As Range, x As Single
Dim wsquote As Worksheet
Set wsquote = Worksheets("sheet1")
On Error Resume Next
Set rng = wsquote.Range("knife2")
If Err.Number = 0 Then
x = rng.Rows.Count
wsquote.Range("knife2").ClearContents
rng.Offset(0, 0).Resize(x).EntireRow.Delete
'Sheets("sheet 1").Range("range").ClearContents
Else
MsgBox "have already deleted the range'"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"gavmer " wrote in message
...
Hi Bob,

Good to hear from you. I tried your code but no error message was
presented. How do i get the error message 'you have already deleted the
range' to appear if the range is no longer.

??????

Cheers!!!!!


---
Message posted from http://www.ExcelForum.com/

Bob,

You are spot on......as always!!!!

Thank you!!!


---
Message posted from http://www.ExcelForum.com/

It's a pleasure as always Gav.

Bob

"gavmer " wrote in message
...
Bob,

You are spot on......as always!!!!

Thank you!!!


---
Message posted from http://www.ExcelForum.com/