Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Clear
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Clear
Try
Sheets("Sheet1").Range("A1:B20").ClearContents Mike On Mar 1, 6:44*am, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Clear
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells.Clear
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 "Abdul" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a Clear button to clear unprotected cells | Excel Programming | |||
Cells.Clear | Excel Discussion (Misc queries) | |||
Clear cells | Excel Programming | |||
clear contents cells of unprotected cells | Excel Programming | |||
Clear cells range if certain cells are all empty | Excel Programming |