CODEWITHSUNDEEP
vbaexcelloopsworksheet
user4333011
user4333011

Reputation:

Excel VBA Loop-Implementation to Clear All Sheets

I'm attempting to clear all the sheets in a workbook from row 3 down. I've accomplished it like this:

With Sheets("Wizard Sheets Missing in Import")
    .Rows("3:" & .Rows.Count).Delete
End With

With Sheets("Import Sheets Missing in Wizard")
    .Rows("3:" & .Rows.Count).Delete
End With

With Sheets("Items Missing from Wizard")
    .Rows("3:" & .Rows.Count).Delete
End With

With Sheets("Items Missing from Imports")
    .Rows("3:" & .Rows.Count).Delete
End With

but would like to implement a loop if possible. I tried this and it only clears the active sheet and none of the other sheets, even though it's being told to loop through the sheets (as far as I'm aware):

For Each vWorksheet In ActiveWorkbook.Sheets
    With ActiveSheet
        Rows("3:" & .Rows.Count).Delete
    End With
Next

Any ideas on how to make a more stream-lined loop for this process?

Upvotes: 0

Views: 1079

Answers (3)

braX
braX

Reputation: 11755

It needs to look more like this

For Each vWorksheet In ActiveWorkbook.Worksheets
    vWorksheet.Rows("3:" & vWorksheet.Rows.Count).Delete
Next

Upvotes: 0

paul bica
paul bica

Reputation: 10715

Option Explicit

Public Sub ClearAllWorksheets()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.UsedRange.Offset(2).Rows.Delete
    Next
End Sub
  • Clears only rows with data, and doesn't Activate or Select objects
  • Works only on the file where the code is running specifically - ThisWorkbook
  • Works only on the Worksheets - doesn't include
    • Chart Sheets
    • Macro sheets (Excel 4.0 XLM files)
    • Dialog sheets (Excel 5.0)

Upvotes: 1

Chuck0185
Chuck0185

Reputation: 531

Sorry, I pasted the wrong code. Try this one. 

Sub DeleteRows()
Dim n As Integer

n = ActiveWorkbook.Worksheets.Count

For x = 1 To n
    Sheets(x).Rows(3 & ":" & Sheets(x).Rows.Count).Delete
Next

End Sub

Upvotes: 0

Related Questions