Cells.Clear

[Abdul]

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

 

[Mike H]

Try

Sheets("Sheet1").Range("A1:B20").ClearContents

Mike

 

[Dave Peterson]

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

 

[Mike Fogleman]

Or another way:
Sub test()
Dim MyRng As Range

Set MyRng = Application.InputBox(Prompt:="Select the range you wish to
keep", _
Title:="Keep Range", Type:=8)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
MyRng.Copy
Sheets.Add.Name = "Temp"
Sheets("Temp").Range("A1").PasteSpecial (xlPasteAll)
Sheets("Sheet1").Cells.ClearContents
Sheets("Temp").UsedRange.Copy
Sheets("Sheet1").Activate
MyRng.PasteSpecial (xlPasteAll)
Sheets("Temp").Delete
Range("A1").Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Mike F