Solved: Deleting Unwanted Rows

[Caddyman]

Hi all

I am using the following VAB Code to check and delete unwanted Rows. But the data I have is about 30,000-40,000 lines, and it takes a long time to run. Is there a quicker way?????

[vba] Do While (Cells(Row, 1).Value <> "")
If Left(Cells(Row, 1), 8) = "List all" Then
Cells(Row, 1).Select
Selection.EntireRow.Delete
ElseIf Cells(Row, 1) = "RSOBranchName" Then
Cells(Row, 1).Select
Selection.EntireRow.Delete
ElseIf Cells(Row, 7) <> "Overdue" Then
Cells(Row, 1).Select
Selection.EntireRow.Delete
Else
Row = Row + 1
End If
Loop
[/vba]

Thanx in Advance
Grant

[dominicb]

Good morning Caddyman

But the data I have is about 30,000-40,000 lines, and it takes a long time to run. Is there a quicker way?????

You will find that more often than not you don't have to select an object to do something with it. Selecting each row before you delete it just takes up necessary time. You can also turn off the screenupdating, which will also speed the macro up - have a look at the amendments I've made below :
[vba]Application.ScreenUpdating = False
Do While (Cells(Row, 1).Value <> "")
If Left(Cells(Row, 1), 8) = "List all" Then
Cells(Row, 1).EntireRow.Delete
ElseIf Cells(Row, 1) = "RSOBranchName" Then
Cells(Row, 1).EntireRow.Delete
ElseIf Cells(Row, 7) <> "Overdue" Then
Cells(Row, 1).EntireRow.Delete
Else
Row = Row + 1
End If
Loop
Application.ScreenUpdating = True[/vba]
HTH

DominicB

[Simon Lloyd]

DominicB wouldn't this kind of method be quicker?
[vba]
Dim Rng As Range
Set Rng = Range("A2:J" & Range("J" & Rows.Count).End(xlUp).Row)
Columns("A:A").AutoFilter Field:=1, Criteria1:="RSOBranchName"
Rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
[/vba]of course for the criteria you could use an array of variants.

[Bob Phillips]

Simon,

What is the purpose of that Selection.Delete at the end?

[Simon Lloyd]

An oversite on my behalf i've fixed it now......flippin' 'eck we will have to rename you to " Old Eagle Eye"

[Bob Phillips]

Well Zack said I had to do my share of the site admin even if it isn't official <g>

[Zack Barresse]

Well Zack said I had to do my share of the site admin even if it isn't official <g>

YES! VINDICATION!!!

[Caddyman]

Thanx All

Dominic's code I understand, , Simon's, well I have never really used ranges to much . I think I shall do some reading on ranges very soon......

Guess that shows how much of a rookie at VBA I really am.