Solved

In Excel, how can I delete a line if column A is blank and column B is blank?

Posted on 2014-12-24
6
65 Views
Last Modified: 2014-12-29
I need to insert a macro which will do the following, in an Excel spreadsheet:

Line by line, the macro should look in the spreadsheet, and if it finds the column A is blank, with no data, and if column B is blank with no data, then it should delete the entire line.
0
Comment
Question by:100questions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40516576
Try this:

Sub DeleteRowIfCols1And2AreBlank()
Dim rng As Range
Dim rangeToDelete As Range
With ActiveSheet
    Set rng = Union(.Columns(1), .Columns(2))
    Set rng = Intersect(.UsedRange, rng)
    Debug.Print rng.Address
    For Each c In rng.Columns(1).Cells
    If c.Value = "" And c.Offset(1, 0).Value = "" Then
        Debug.Print "will delete row " & c.Row
        'c.EntireRow.Hidden = True
        If rangeToDelete Is Nothing Then
            Set rangeToDelete = c.EntireRow
        Else
            Set rangeToDelete = Union(c.EntireRow, rangeToDelete)
        End If
    End If
    Next
    Debug.Print rangeToDelete.Address
    rangeToDelete.Delete
End With
End Sub

Open in new window


Basically there are two methods with delete, either step backwards from the last row upwards using a counter, or iterate through a range, building a 'range to delete' and then delete it at the end. It is this second approach that i've used here.
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40516668
Here's a 3rd, quick and easy way.

Sub FiliterIt()
Dim rng As Range

Set rng = Range("A1:B" & ActiveSheet.UsedRange.Rows.Count)
' Clear any existing filter
rng.AutoFilter

With rng
 .AutoFilter Field:=1, Criteria1:="", Operator:=xlAnd, Field:=2, Criteria1:=""
 .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:Simon
ID: 40516698
Touché, Martin :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:Martin Liss
ID: 40516764
Thanks.
0
 

Author Closing Comment

by:100questions
ID: 40521928
Thank you.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40521973
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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