We help IT Professionals succeed at work.

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

81 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.
Comment
Watch Question

SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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.
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
Touché, Martin :)
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thanks.

Author

Commented:
Thank you.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.