Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to clear contents of certain cells MrAcquire Excel Discussion (Misc queries) 3 February 11th 10 06:25 PM
How to clear multiple cells of input data in Excel simultaneously sstea Excel Worksheet Functions 12 May 2nd 09 08:47 PM
In Excel how do I update cells in multiple sheets simultaneously? kjh1210 Excel Discussion (Misc queries) 1 April 22nd 09 11:01 PM
Clear Contents of multiple continuous ranges Mlawrence Excel Programming 2 February 28th 08 10:16 PM
Clear Contents Of Cells Where Value = 0 carl Excel Worksheet Functions 3 July 6th 07 06:02 PM


All times are GMT +1. The time now is 12:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"