CODEWITHSUNDEEP
vbaexcel
user7431724
user7431724

Reputation: 11

Offset/Resize previously defined range

I am trying to clear everything (including images) from the cells on sheets 4 to 9 and then also 12 and 13. I have the following code, but it's clearing the contents from sheets 3-9 and 12-15 and I have no idea why.

Any thoughts?

Sub ClearContents()
Dim i As Integer
For i = 4 To 9 And 12 And 13
Sheets(i).Cells.Clear
Sheets(i).Pictures.Delete

Next i

Upvotes: 1

Views: 77

Answers (2)

user3598756
user3598756

Reputation: 29421

Edited to use sheet names instead of their index

you could use the Sheets(array) flavour of Sheets collection:

Option Explicit

Sub ClearContents()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets(Array("Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet12", "Sheet13")) ' loop through wanted worksheets
        ws.Cells.Clear
        ws.Pictures.Delete
    Next
End Sub

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33692

Try the code below:

Option Explicit

Sub ClearContents()

    Dim i As Long
    Dim Sht As Worksheet
    Dim PicObj As Object

    ' loop through all worksheets in your workbook
    For Each Sht In ThisWorkbook.Sheets
        Select Case Sht.Index
            Case 4 To 9, 12, 13
                Sht.Cells.Clear

                ' loop through all shapes in your worksheet and delete them all
                For Each PicObj In Sht.Shapes
                    PicObj.Delete
                Next PicObj

            Case Else '<-- for future cases, if you'll need
                'do nothing
        End Select
    Next Sht

End Sub

Upvotes: 2

Related Questions