Deleting Rows Where Cells in Multiple Columns are Blank

[sbarski]

I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.

Here's an example of what I'm working with:
[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This is what I need the final product to look like:
[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]
So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column.
How can I quickly delete the rows where both columns are empty?

Thank you!

 

[Michael M]

Hi and welcome to the Board
Try this macro

Sub delem()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
    If Range("A" & r).Value = "" And Range("B" & r).Value = "" Then Rows(r).Delete
Next r
End Sub

 

[sbarski]

Michael,

Thanks for the reply. I really don't understand macros. Is there a way to do it without using one?

 

[Peter_SSs]

Here's a fairly quick manual way.

1. If the data does not have headers, add some (insert a new row if needed)

2. Select the whole data area, including headings and apply AutoFilter.

3. Filter each column to show (Blanks)

4. Excluding the headings, select from the row immediately below the headings to below the last hidden row and delete the rows

5. Remove the AutoFilter altogether, or set each column's filter back to (Select All)

 

[sbarski]

Thanks Peter! I just tried it out on a small scale and it seemed to work perfectly. I'm excited to try it out at work tomorrow with one of the 100+ row spreadsheets!

 

[sbarski]

Just tried it out on one of my spreadsheets at work - 350 rows down to only the ones I needed in 10 seconds!! Thank you Peter, your solution worked perfectly!!!

 

[PeterH95]

Just to note this manual fix is so easy - thanks for that. Once I performed the changes I had a list with lots of blanks but that's easy to fix.