excel macro - how to delete rows based on a cell value

Question

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

Solution

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.

Comments

[Maraboc]

What did you try so far ??

[CharlieRB]

Welcome to Super User. This is not a service site for someone to write macros for you. This is a Q&A for us to help you with specific questions about what you are trying to do and get stuck. Reading How to Ask may help you improve your question by showing what you have already done and what you are working with so far. We can help you from there.

[DanM]

here is what i am trying to do in a sub

[DanM]

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

[DanM]

thanks for the macro. however when i run it, i get a "run-time error '9': Subscript out of range. can you review the code and comment on why this is happening>=?

[jcbermu]

My bad!!! Change the "Hoja1" on the line Set wks = wkb.Sheets("Hoja1")to the name of your worksheet.

[DanM]

ok now the routine does not error out. however it kept the rows with a 0 and deleted the rest. need the opposite.

[jcbermu]

The variable filtercolumn = "G"indicates the column to check. Is it G?

[DanM]

so my bad. i didn't copy some data into column g for the rows that i wanted to keep. now when i run your routine, it doesn't delete anything. and i verified that column g is the correct column.

[DanM]

so i figured out how to make it work. i had sorted column g from highest to lowest, i just reversed that and the macro works. but can you tell me how to make it work when the column sort is highest to lowest?

[DanM]

now how do i make the answer above a positive 1 (it now says -2). i will use the routine as is. thanks jcbemu!

[jcbermu]

It should work always, no matter if the column is sorted or not. The macro detects how many rows the sheet has counting cells on column A, then in all cells of column A must be some data, cannot be empty.