CODEWITHSUNDEEP
vbaexcel
John English
John English

Reputation: 121

Deleting content in multiple sheets - Excel VBA

Private Sub CommandButton2_Click()

sheetNo = 1

With Worksheets("Sheet" & sheetNo)
.Range("A1:B12").ClearContents
End With
sheetNo = sheetNo + 1

End Sub

Assume I have 10 sheets (Sheet1, Sheet2, Sheet 3,........) and when I click CommandButton2 it should delete whatever the content in the range A1:B12. But this code deletes the content only in the Sheet1. can someone tell me why and where have I gone wrong?

Thanks.

Upvotes: 2

Views: 18901

Answers (1)

user2140173
user2140173

Reputation:

See how to use loops
these are just 3 simple loops examples ( all 3 are doing the same thing ) for you to better understand how loops work

Private Sub CommandButton2_Click()

    ' using for each loop
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        ws.Range("A1:B12").ClearContents
    Next

    ' using for loop with an iterator
    Dim i As Long
    For i = 1 To Sheets.Count
        Sheets(i).Range("A1:B12").ClearContents
    Next i

    ' using do while loop
    i = 1
    Do While i <= Sheets.Count
        Sheets(i).Range("A1:B12").ClearContents
        i = i + 1
    Loop

End Sub

Upvotes: 4

Related Questions