PDA

View Full Version : Solved: Delete rows if cells in multiple columns are blank



starsky
04-05-2011, 05:07 AM
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
04-05-2011, 03:55 PM
Look at SpecialCells to select the ranges to delete

starsky
04-06-2011, 04:50 AM
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
04-06-2011, 05:07 AM
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
04-07-2011, 08:36 AM
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.

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

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
04-07-2011, 09:45 AM
It was deleting every other row. Same thing happens with this variation.

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

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

RonMcK
04-07-2011, 09:57 AM
You might find this thread helpful: http://www.vbaexpress.com/forum/showthread.php?t=36798&highlight=delete+blank+rows
and this one:
http://www.vbaexpress.com/forum/showthread.php?t=36835&highlight=delete+blank+rows

Cheers,

starsky
04-07-2011, 10:07 AM
Thanks, will take a look. I've used a solution that involves a formula, as suggested by tilamok. It seems to work.
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