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 :yes