Using VBA to Delete Specified Rows

Probably an easy, easy question:  I am working on a recorded macro in VBA to reformat an Excel spreadsheet.  Got all I needed except for deleting a row if the D cell is zero (not empty).  How would I write the code to delete the row if D# is 0?  With an IF statement?

Thank you.

Pat
FFNStaffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Sub Macro()
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
For rwNum = lastRow To 1 Step -1
    With Cells(rwNum, "D")
        If .Text = "0" Then
           Rows(rwNum).EntireRow.Delete
        End If
    End With
  Next
End Sub

Open in new window

Regards
0
nutschCommented:
You can use a if statement in a loop, but the fastest way is with an autofilter as in the following example:

With [A1].CurrentRegion
.AutoFilter
.AutoFilter 4, "=0", xlOr, "=" 'the 4 is for the 4th column in the range (D vs A)
.Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Delete
.AutoFilter
End With

Open in new window


Thomas
0
Rgonzo1971Commented:
Hi,

Or if  Zero is the result of a formula

Sub Macro()
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
For rwNum = lastRow To 1 Step -1
    With Cells(rwNum, "D")
        If .Value = "0" Then
           Rows(rwNum).EntireRow.Delete
        End If
    End With
  Next
End Sub

Open in new window

Regards
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

FFNStaffAuthor Commented:
Rgonzo, Thomas,

Thanks for your responses.  

I copied your code (individually) into my code (shown below) right below End With, less your End With/End Sub.  Both stopped at End Sub without completing the task.  Should I have left out something else?  What am I missing ?

Novice would be a step up for me as far as experience with VBA so please pardon my ignorance.  


Sub Royal()
'
' Macro
'

    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "GDC"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Rep"
    Columns("A:D").Select
    Columns("A:D").EntireColumn.AutoFit
    Columns("D:D").Select
    Selection.Style = "Comma"
    Range("C1").Select
    ActiveWorkbook.Worksheets("LOB_Stage1").Sort.SortFields.Clear
       ActiveWorkbook.Worksheets("LOB_Stage1").Sort.SortFields.Add Key:=Range( _
        "d2:d2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("LOB_Stage1").Sort
        .SetRange Range("A1:D2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
 
  End With

End Sub
0
Rgonzo1971Commented:
Hi,

This should do it

Sub Royal()
'
    Columns("B:B").Delete
    Columns("D:F").Delete
    Columns("E:E").Delete
    Range("D1").FormulaR1C1 = "GDC"
    Range("A1").FormulaR1C1 = "Rep"
    Columns("A:D").EntireColumn.AutoFit
    Columns("D:D").Style = "Comma"
    Range("C1").Select
    ActiveWorkbook.Worksheets("LOB_Stage1").Sort.SortFields.Clear
       ActiveWorkbook.Worksheets("LOB_Stage1").Sort.SortFields.Add Key:=Range( _
        "d2:d2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("LOB_Stage1").Sort
        .SetRange Range("A1:D2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
  
  End With
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row
    For rwNum = LastRow To 1 Step -1
        With Cells(rwNum, "D")
            If .Value = "0" Then
                Rows(rwNum).EntireRow.Delete
            End If
        End With
  Next

 
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FFNStaffAuthor Commented:
Thank you both for your help.  

Rgonzo, this worked wonderfully.  Thank you for cleaning up the code too!

Pat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.