Clear contents in selected sheets

Halvar · Mar 18th 2014

Hello,

I'm trying to clear all contents on 3 selected sheets (out of many worksheets I've got in my WB). So far I've got the following code (which works fine), but I was wondering if there's an easier way to get the same result:

Code

Private Sub ClearData()
    
        Dim ws As Worksheet
        
        For Each ws In Worksheets
            Select Case ws.Name
            Case "Sheet1", "Sheet2", "Sheet3"
                With ws
                    ws.Cells.ClearContents
                End With
            End Select
        Next ws

Display More

I was trying to see if it's possible to use Array, but couldn't find anything relevant so far. Would appreciate your help!

Smallman · Mar 18th 2014

Re: Clear contents in selected sheets

Hi Halvar

Welcome to Ozgrid. Since you have hard coded your sheet names into vb then perhaps something like;

Code

Sub Remove()    
Sheets([{"Sheet1", "Sheet2", "Sheet3"}]).Select
    Cells.ClearContents
End Sub

But let the group know if you are looking for something more dynamic. Like clearing the selected sheets. This would mean it would clear the sheets you select.

Take care

Smallman

Halvar · Mar 19th 2014

Re: Clear contents in selected sheets

Hi Smallman, and thanks for a quick reply. I've tried to use the solution you've posted, but unfortunately the results are somewhat different from what I've expected: the code selects all sheets, but clears contents only on the first one.

I've played out with it just a bit and found the following way to achieve the result I was looking for:

Code

Sub ClearSheets()
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Cells.Select
    Selection.Clear
End Sub

This will select the 3 sheets that need to be cleared, and will remove all contents and formatting. In case someone else is looking for a similar solution, changing "Selection.Clear" to "Selection.ClearContents" will keep the formatting but will remove all the values.

But thanks again for your help, it drove me in the right direction!

Smallman · Mar 19th 2014

Re: Clear contents in selected sheets

Hi Halvar

Thankyou so much for posting that solution and explanation. It really adds to the thread. I like your clear explanation at the bottom. Good job. On further tinkering here is another option.

Code

Sub ClearEm()
   Sheets([{"Sheet1", "Sheet2", "Sheet3"}]).Item(1).Cells.ClearContents
   Sheets([{"Sheet1", "Sheet2", "Sheet3"}]).FillAcrossSheets Sheets([{"Sheet1", "Sheet2", "Sheet3"}]).Item(1).Cells, 1
End Sub

The 1 a the end of line 2 is short for xlFillWithContents.

Take care

Smallman

Halvar · Mar 20th 2014

Re: Clear contents in selected sheets

Thank you;) I've just started using VBA, so it's always nice to see a different way to get to the same spot!

Clear contents in selected sheets

Participate now!

Share