Solved: Delete rows if cells in multiple columns are blank

[starsky]

Hi,

I want to be able to delete rows in a table of 3-4000 rows where cells in columns K to AO are blank. Is there a way to do this without applying autofilter to each of those columns?

There are plenty of solutions for single column criteria, but I can't find anything for multiple columns.

Thanks.

[mdmackillop]

Look at SpecialCells to select the ranges to delete

[starsky]

I did, and also worksheet functions. My difficulty has been combining some way of looping through each row with data, and then deleting the row if the cells in columns K:AO are blank.

[tilamok]

Why don't u put a new formula in column AP that concatenates cols K:ao.
Then filter by column ap for blanks
Then delete the blank rows

[starsky]

I could use a formula, but would prefer something simpler. I want a short piece of code that will be run monthly. The range of columns will change with the month, but I'll cross that bridge later (probably easy enough).

This is what I was just experimenting with.

[VBA]Dim rw As Range
For Each rw In Selection.Rows
If Application.WorksheetFunction.Sum(Range("K:AO")) = 0 Then
rw.EntireRow.Delete
End If
Next rw[/VBA]

It deleted a couple of thousand rows out of 4500, but I'm not sure why, probably related to how it looped through everything. It certainly wasn't based on the criteria I want.

I basically want to combine a test of contigous cells in a row with a looping routine that runs through each row. I've not been able to make that combination using SpecialCells either. My guess is how that range is referenced each time.

[starsky]

It was deleting every other row. Same thing happens with this variation.

[VBA]Dim C As Range
Dim X, i As Integer
X = ActiveCell.Column
For i = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
Set C = Cells(i, X)
If Application.WorksheetFunction.Sum(Range("K" & i, "AO" & i)) = 0 Then
C.EntireRow.Delete
End If
Next i[/VBA]

I'm thinking you can't test a fixed range of cells in each looped row.

[RonMcK]

You might find this thread helpful: http://www.vbaexpress.com/forum/show...ete+blank+rows
and this one:
http://www.vbaexpress.com/forum/show...ete+blank+rows

Cheers,

[starsky]

Thanks, will take a look. I've used a solution that involves a formula, as suggested by tilamok. It seems to work.
[VBA]Dim i As Integer
Dim Last As Long
Last = Cells(Rows.Count, "F").End(xlUp).Row
Range("AP2").Value = "=COUNTA(K2:AO2)"
Range("AP2").AutoFill Destination:=Range("AP2:AP" & Last)
For i = Last To 2 Step -1
If (Cells(i, "AP").Value) = "0" Then
Cells(i, "AP").EntireRow.Delete
End If
Next i[/VBA]