CODEWITHSUNDEEP
excelvba
Yousuf
Yousuf

Reputation: 123

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

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

Upvotes: 1

Views: 131

Answers (2)

cybernetic.nomad
cybernetic.nomad

Reputation: 6418

Try a different approach, delete the first row over and over gain, until the content of A1 is not blank:

Do Until Ws.Range("A1").Value <> vbNullString
    Ws.Rows(1).Delete
Loop

Upvotes: 0

Mech
Mech

Reputation: 4015

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

Upvotes: 0

Related Questions