Clear contents code for partial workbook

tdcsi · Sep 20, 2011

I am running Excel 2010 and I'll admit upfront I know very little about macro's and Visual basic. I have a workbook containing 19 worksheets. The majority of the cells are protected, but I need to leave a few open for data entry. I am trying to write a code that will clear contents of all open cells when someone closes the workbook. The following code does work, but I really only need it to go through the first two worksheets; the rest are locked and it takes longer then I would like to run through the entire code. Is there a way to limit this code to only include the first two worksheets?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, c As Range
For Each ws In Me.Worksheets
With ws
.Unprotect Password:="28080"
For Each c In .UsedRange.SpecialCells(xlCellTypeConstants)
If Not c.Locked Then c.MergeArea.ClearContents
Next c
.Protect Password:="28080"
End With
Next ws
End Sub

Joe4 · Sep 21, 2011

Welcome to the Board!

You can loop through just the first two worksheets by making use of the sheet index, i.e.

Code:

    Dim i As Integer
 
    For i = 1 To 2
        With Sheets(i)
           ...

tdcsi · Sep 21, 2011

Thank you, with a little "learning" on my part it now works perfectly.

Clear contents code for partial workbook

tdcsi

Board Regular

Excel Facts

Joe4

MrExcel MVP, Junior Admin

tdcsi

Board Regular

Similar threads

Forum statistics

Share this page

Clear contents code for partial workbook

tdcsi

Board Regular

Excel Facts

Joe4

MrExcel MVP, Junior Admin

tdcsi

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock