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
63 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
  • 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 46

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 40516764
Thanks.
0
 

Author Closing Comment

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

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

832 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