Mac – excel macro – how to delete rows based on a cell value

macrosmicrosoft excelworksheet-function

I have the following spreadsheet

**Columns a-i**

A    B               C       D      E      F    **G**   H       I

**Row 1**

003 His Dressing    3305    S4S     0      0    **35**  35      1           

**Row 2**

003 His Dressing    Bidding   0      0     0    **0**   0        0

I need a macro that will delete row 2 based on cell g being 0, but ignore row 1 as the value in column g is 35.

of course my spreadsheet has multiple rows that have column g with a value of 0, need to delete all the rows in the workbook.

so here is the sub that has an error in it, dont know why.

Sub DeleteRows()
    Dim rownum As Long
    for rownum 1 to 1000
    If Cells(rownum, 1).Value = 0 Then
        Rows(rownum).Delete
    Next rownum
    Cells(rownum, 1).Activate
End Sub

Best Answer

Here it is:

Public Sub removeRows()
    Application.ScreenUpdating = False
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    filtercolumn = "G"
    Set wks = wkb.Sheets("Sheet1")
    totalrows = wks.Cells(Rows.Count, "A").End(xlUp).Row
    For j = totalrows To 1 Step -1
        If wks.Cells(j, filtercolumn) = 0 Then
            wks.Rows(j).Delete
        End If
    Next j
    Application.ScreenUpdating = True
    themessage = MsgBox("Finished", vbInformation)
End Sub

Open Macros with ALT + F11, inser a new module under ThisWorkbook, and paste the code on the right side.

Related Question