VBA code won't iterate through worksheets to delete defined rows

Question

I have 100 worksheets in a workbook. In each sheet, there are blank rows for the first rows. For some sheets, the 8th row is where the data begins. For some sheets data begins on the 9th or 10th.

My code goes to the first row that has a value and then offset one row up. Then i need it to delete.

My code works on a single sheet just fine, but when i try to iterate through all the worksheets in the workbook, it doesn't go beyond the active worksheet.

What can i do to iterate through the worksheets?

Sub To_Delete_Rows_In_Range()
Dim iCntr
Dim rng As Range
Dim wb As Workbook
Set wb = ActiveWorkbook

For Each Ws In wb.Worksheets
    Set rng = Range("A1", Range("A1").End(xlDown).Offset(-1, 0))
    For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
        Rows(iCntr).EntireRow.Delete
    Next
Next Ws

End Sub

You need to qualify your ranges: Ws.Range("A1"... instead of Range("A1"... — cybernetic.nomad
Why are you deleting in a loop? Just delete the entire range. — BigBen
@BigBen I did rng.EntireRow.Delete it deleted what i needed to, but when it went on to the second loop, it gave an error. "Delete method of Range class failed." — Yousuf

Mech Mech · Accepted Answer · 2020-03-05T19:53:19

Added Ws. in front of your range so it knows to change with the sheet.

Sub To_Delete_Rows_In_Range()
Dim iCntr
Dim rng As Range
Dim wb As Workbook
Set wb = ActiveWorkbook

For Each Ws In wb.Worksheets
    Set rng = Ws.Range("A1", Ws.Range("A1").End(xlDown).Offset(-1, 0))
    For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
        Ws.Rows(iCntr).EntireRow.Delete
    Next
Next Ws

End Sub

VBA code won't iterate through worksheets to delete defined rows

2 Answers