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
60 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:SimonAdept
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 45

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:SimonAdept
ID: 40516698
Touché, Martin :)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Martin Liss
ID: 40516764
Thanks.
0
 

Author Closing Comment

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

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 2010 - Text not aligning at bottom of cell! 5 17
Excel 3 22
File size limit in SharePoint 2010 3 15
Macro 3 20
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

17 Experts available now in Live!

Get 1:1 Help Now