• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

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

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
100questions
Asked:
100questions
  • 3
  • 2
1 Solution
 
SimonCommented:
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
 
Martin LissOlder than dirtCommented:
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
 
SimonCommented:
Touché, Martin :)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Martin LissOlder than dirtCommented:
Thanks.
0
 
100questionsAuthor Commented:
Thank you.
0
 
Martin LissOlder than dirtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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