Solved

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

Posted on 2015-02-19
2
57 Views
Last Modified: 2015-02-25
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
Comment
Question by:100questions
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40619753
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
 
LVL 33

Expert Comment

by:Norie
ID: 40619766
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now