0

The problem is really simple but I could not find any efficient solution. I have a table. I want to loop through and delete the entire row when some condition are met. The code I use is the following:

Range("H3").Select
Range(Selection, Selection.End(xlDown)).Select
Dim cellule As Range
For Each cellule In Selection.Cells    
If cellule.Value = "--" And ...(More condition) Then
        cellule.EntireRow.Delete
    End If
Next cellule

It actually works but when two consecutive rows met those condition then the 2nd one is not deleted because it has gone up when the 1st had been deleted. Basically the 2nd has been skipped.

Community
  • 1
  • 1
user3516255
  • 31
  • 2
  • I actually found the solution using a Do...Loop while which is more adapted in that case. – user3516255 Apr 09 '14 at 22:11
  • 1
    When deleting rows you should work from the bottom up - then you don't run into the problem you're describing. – Tim Williams Apr 10 '14 at 00:30
  • That's a pretty simple good one too. I had never thought about it. Thanks for the tip – user3516255 Apr 11 '14 at 20:24
  • also, you could do this without a loop either using AutoFilter or SpecialCells function. Take a look: [SO: How to delete multiple rows without a loop](http://stackoverflow.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba) – Our Man in Bananas May 27 '14 at 21:21

1 Answers1

0

you can do this without a loop if you want:

first, add a formula in an empty column that will show the #NA error if your condition is true:

sFormula = "=IF(H:H=""- -"",NA(),"""")" ' could combine conditions using AND Excel worksheet functionn

' put the formula in column K for all rows, if the condition in col H is met, 
' this row will show an #NA error in column K   
Range("K2:K" & Range("H" & rows.count).End(xlUp).Row).Formula = sFormula

' now match all the rows using SpecialCells in one step, and delete!
Range("K5:H" & Range("H" & rows.count).End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).entirerow.delete shift:=xlup

this is similar to striking the F5 key in Excel and clicking 'SpecialCells', then choosing 'Errors'

let us know how you get on

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148