Thread: delete row
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
[email protected] hewett_nick@hotmail.com is offline
external usenet poster
 
Posts: 16
Default delete row

You might want to try adapting this code from an earlier post by Chip
Pearson...

Sub AAA()
Dim RangeToDelete As Range
Dim iCountA
Dim RowNdx As Long
iCountA = 100
For RowNdx = 2 To iCountA
If Rows(RowNdx).Cells(1, "B") = 0 Or _
Rows(RowNdx).Cells(1, "B") = 2 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = Rows(RowNdx)
Else
Set RangeToDelete = Application.Union(RangeToDelete,
Rows(RowNdx))
End If
End If
Next RowNdx
RangeToDelete.EntireRow.Delete
End Sub

As well as being fast it avoids the problem that you are likely to run
into using your existing method which is that each time you delete a
row the next row will be skipped by the loop.
I.e. suppose i = 3 and Cells(i, 7).Value = False
Row 3 will get deleted, as you intend, BUT row 4 then becomes row 3.
Next time round the loop though i becomes 4 so the new row 3 gets
skipped.

Br, Nick H