Clear contents only

[austenr]

is there a way to clear the contents only of selected cells in all worksheets at time of open?

[Zack Barresse]

Hi Austen,

This is possible. But is there a set range of cells you wish to clear on all sheets? Or are you wanting to select the cells, then clear them? An example of clearing the same cells on each sheet would look something like this ...

Option Explicit

Private Sub Workbook_Open()
    Dim ws As Worksheet, sht1 As Worksheet
    Set sht1 = ActiveSheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A1:E7").ClearContents
    Next ws
    sht1.Activate
    Application.ScreenUpdating = True
End Sub

[austenr]

As usual firefytr works great. One question how can I adapt it to work on different cells on each worksheet? Just suggest no code. Thanks.

[Zack Barresse]

Well, that gets a little trickier. I'd basically add different ranges and maybe do a select case statement.

Example:

Select Case ws.Name
    Case "Sheet1"
        ws.Range("A1:E7").ClearContents
    Case "Sheet2", "Sheet3"
        ws.Range("A5:E10").ClearContents
    Case Else
        ws.Range("G1:G5").ClearContents
    End Select

The first Case example can be used for a single sheet, the second Case can be used for ranges that are the same in multiple worksheets, and the Case Else can be used for any other sheets that contain the same range.

HTH