Link to home
Start Free TrialLog in
Avatar of E=mc2
E=mc2Flag for Canada

asked on

How can I modify this Excel macro to delete a row if the A field is blank, and the B field is 0?

I want to modify this macro so that if the A field is blank, and the corresponding B field has '0' in it, or '0.00', then delete the entire row.


  Sub Step2_DeleteBlankRows()

            'Deletes the entire row within the selection if the ENTIRE row contains no data.

            'We use Long in case they have over 32,767 rows selected.

            Dim i As Long

                'We turn off calculation and screenupdating to speed up the macro.

                With Application

                    .Calculation = xlCalculationManual

                    .ScreenUpdating = False

                'We work backwards because we are deleting rows.

                For i = Selection.Rows.Count To 1 Step -1

                    If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

                        Selection.Rows(i).EntireRow.Delete

                    End If

                Next i

                    .Calculation = xlCalculationAutomatic

                    .ScreenUpdating = True

                End With

            End Sub
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie
Norie

Perhaps.
                For i = Selection.Rows.Count To 1 Step -1

                    If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Or (Selection.Cells(i, 1)="" And Selection.Cells(i, 2)=0) Then

                        Selection.Rows(i).EntireRow.Delete

                    End If

                Next i

Open in new window