Clear all contents of selected sheets in VBA?

[thommo41]

Hi,

Quite simply, I want to clear the contents of a selection of named sheets.
For example, I might have a book with 5 sheets (named "sheet1", "sheet2" etc) and I want to clear the contents of "Sheet1", "Sheet3" and "Sheet4".
I can code select sheet 1, clear contents, then 3, clear contents etc, but there is a much better, shorter way of coding this.
Thanks in advance
Alan

 

[VoG]

Try like this

Sub ClrSheets()
Dim ws As Worksheet
For Each ws In Worksheets(Array("Sheet1", "Sheet3", "Sheet4"))
    ws.UsedRange.ClearContents
Next ws
End Sub

 

[thommo41]

Thank you!

 

[mikerickson]

This is a non-looping version.

Sub test()
    With ThisWorkbook.Sheets(Array("Sheet2", "Sheet3", "Sheet4"))
        .Item(1).Cells.ClearContents
        .FillAcrossSheets .Item(1).Cells, xlFillWithContents
    End With
End Sub

Which prompted a question, if the user selects multiple sheets, how can one find which sheets have been selected?

Nope, I shouldn't hijack this thread, so ... http://www.mrexcel.com/forum/showthread.php?p=2754771#post2754771