One way:

Option Explicit
Sub testme()
Dim TempWks As Worksheet
Dim AddrToClear As String
Dim CurWks As Worksheet
Dim myRng As Range

Set CurWks = Worksheets("Sheet1")

Set myRng = Nothing
On Error Resume Next
Set myRng = CurWks.Range("MyRange")
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no range with that name on the sheet"
Exit Sub
End If

Application.ScreenUpdating = False

Set TempWks = Workbooks.Add(1).Worksheets(1)

With TempWks
.Range(CurWks.UsedRange.Address).Value = 1
.Range(myRng.Address).Clear
AddrToClear = ""
On Error Resume Next
AddrToClear = .Cells.SpecialCells(xlCellTypeConstants).Address
On Error GoTo 0
.Parent.Close savechanges:=False
End With

Application.ScreenUpdating = True

If AddrToClear = "" Then
MsgBox "nothing to clear"
Else
CurWks.Range(AddrToClear).Clear
End If

End Sub


Abdul wrote:

Hi,

Worksheets("Sheet1").Cells.Clear
will clear the sheet.

If there a way that we can clear Cells less defined range?

some thing like Cells excluding "MyRange" .clear? so that t will be so
fast and without using loop

thanks

--

Dave Peterson