CODEWITHSUNDEEP
excelvba
user3750320
user3750320

Reputation: 1

Excel macro to delete all rows on all worksheets if the value in column AA = 0

I have a workbook containing 197 sheets. I need to delete all rows in each sheet if the value in column AA is zero. Anyone know how to do this?

Upvotes: 0

Views: 1650

Answers (1)

Greg
Greg

Reputation: 426

If you would like to delete each row if and only if that row's column AA is zero, then the below should work for you.

Sub delete0rows()
    Dim Worksheet As Excel.Worksheet
    Dim lastRow As Long
    Dim i As Integer
    For Each Worksheet In Application.ThisWorkbook.Worksheets
        lastRow = Worksheet.Cells(Rows.Count, 1).End(xlUp).Row
        i = 1
        Do While i <= lastRow
            If Worksheet.Range("AA" & i).Value = 0 Then
                Worksheet.Rows(i).Delete
                i = i - 1
                lastRow = lastRow - 1
            End If
            i = i + 1
        Loop
    Next
End Sub

Note this will only delete the row if the cell value in AA is 0. There are several subtleties here... Excel will show a 0 even if the cell value is '0 or =0 among other things, and those rows will not be deleted with the above code.

Upvotes: 1

Related Questions