delete row

[fi.or.jp.de]

i am using this code to delete the rows if its value is true

This slow, It process at speed of 7 Minute per 1000 rows

I am using below code

Sub remove()
Worksheets("sheet3").Range("G2").Select
Rng = Cells(Rows.Count, "C").End(xlUp).Row
starter = Timer
For i = 1 To Rng
If Cells(i, 7).Value = False Then
Rows(i).Delete
Else
End If
Next
MsgBox Format(Timer - starttime, "00:00:00")
End Sub

 

[Mike H]

Hi,

If you delete rows on the fly then you have to go backwards through the
range or risk missing rows. Try this which should speed matters up. Howwever
7 minutes per 1000 rows does seem a very long time!

Sub remove()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
starttime = Timer
rng = Cells(Rows.Count, "c").End(xlUp).Row
For i = rng To 1 Step -1
If Cells(i, 7).Value = False Then Rows(i).Delete
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Format(Timer - starttime, "00:00:00")
End Sub

Mike

 

[CurlyDave]

Try This

Sub remove()
Worksheets("Sheet1").Range("G2").Select
Rng = Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False
'starter = Timer
For i = Rng To 2 Step -1
If Cells(i, 7).Value = False Then Rows(i).Delete
Next
'MsgBox Format(Timer - starttime, "00:00:00")
End Sub

 

[hewett_nick]

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