PDA

View Full Version : delete entire rows with criteria



alienscript
11-09-2008, 12:58 AM
Hello VB expert,

I am trying to have the entire rows to be deleted if the specified cells are all empty. I tried with Union but not sure how the next syntax should be. Excel is XP office 2003 and I have rows in "group and outline" enabled. Thanks in advance.



Dim i As Long, lastrow As Long, MyRng as Range
lastrow = Cells(65536, 1).End(xlUp).Row

For i = 6 To lastrow
Set MyRng = Application.Union(Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 6), Cells(i, 7), Cells(i, 8), Cells(i, 10), _
Cells(i, 11), Cells(i, 12), Cells(i, 14), Cells(i, 15), Cells(i, 16), Cells(i, 18), Cells(i, 19), Cells(i, 20))

Bob Phillips
11-09-2008, 03:13 AM
Wat are your criteria? delete the row if all cells are blank, some,. more than a number?

Tell us your rule.

alienscript
11-09-2008, 06:18 AM
I apologized I should have mentioned more precisely.

From row 6 to last used row, only when Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 6), Cells(i, 7), Cells(i, 8), Cells(i, 10), Cells(i, 11), Cells(i, 12), Cells(i, 14), Cells(i, 15), Cells(i, 16), Cells(i, 18), Cells(i, 19) and Cells(i, 20) are all empty, that row must be deleted. This should continue until last row.

Hope it is clear now and awaiting your assistance.

Bob Phillips
11-09-2008, 06:43 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 6 Step -1

If Application.CountA(.Cells(i, "A").Resize(, 24)) <= 6 Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True

End Sub

alienscript
11-09-2008, 07:27 AM
Thanks a lot. It is a lot faster than using Cell(i,2)....Cell(i,n)