Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to clear contents of multiple cells in Excel simultaneously
I would like to allow users to clear the contents from all of the unprotected
cells in multiple sheets of a workbook, simultaneously. I read a similar posting on the subject in microsoft.public.excel.worksheet.functions, posted by sstea, titled How to clear multiple cells of input data in Excel simultaneously. The suggested code was as follows: Sub ClearForm() Dim c As Range For Each c In Sheets("sheet1").UsedRange If c.Locked = False Then c.ClearContents End If Next End Sub This was a good start but I ran into errors. 1. Runtime error '1004'. Cannot change part of a merged cell. How do I edit this code to clear the contents of unprotected merged cells? 2. How do I make this simultaneously work on multiple sheets in the workbook? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to clear contents of multiple cells in Excel simultaneously
Change:
c.ClearContents to c.Value = "" And to get all the worksheets: Sub ClearForm() Dim c As Range dim wks as worksheet for each wks in activeworkbook.worksheets For Each c In Sheets("sheet1").UsedRange.Cells If c.Locked = False Then c.value = "" end if next c next wks End Sub Jaclyn wrote: I would like to allow users to clear the contents from all of the unprotected cells in multiple sheets of a workbook, simultaneously. I read a similar posting on the subject in microsoft.public.excel.worksheet.functions, posted by sstea, titled How to clear multiple cells of input data in Excel simultaneously. The suggested code was as follows: Sub ClearForm() Dim c As Range For Each c In Sheets("sheet1").UsedRange If c.Locked = False Then c.ClearContents End If Next End Sub This was a good start but I ran into errors. 1. Runtime error '1004'. Cannot change part of a merged cell. How do I edit this code to clear the contents of unprotected merged cells? 2. How do I make this simultaneously work on multiple sheets in the workbook? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear contents of certain cells | Excel Discussion (Misc queries) | |||
How to clear multiple cells of input data in Excel simultaneously | Excel Worksheet Functions | |||
In Excel how do I update cells in multiple sheets simultaneously? | Excel Discussion (Misc queries) | |||
Clear Contents of multiple continuous ranges | Excel Programming | |||
Clear Contents Of Cells Where Value = 0 | Excel Worksheet Functions |