Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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
0
100questions
Asked:
100questions
1 Solution
 
Phillip BurtonCommented:
Try changing

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

to

If Selection.cells(i,1)="" and (Selection.cells(i,2)=0 or Selection.cells(i,2)="0.00") Then
0
 
NorieCommented:
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

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now