PDA

View Full Version : Solved: Deleting Unwanted Rows



Caddyman
05-08-2008, 10:45 PM
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?????:dunno

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


Thanx in Advance
Grant

dominicb
05-09-2008, 12:38 AM
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?????:dunno

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 :
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
HTH

DominicB

Simon Lloyd
05-09-2008, 12:52 AM
DominicB wouldn't this kind of method be quicker?

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
of course for the criteria you could use an array of variants.

Bob Phillips
05-09-2008, 02:23 AM
Simon,

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

Simon Lloyd
05-09-2008, 03:04 AM
An oversite on my behalf i've fixed it now......flippin' 'eck we will have to rename you to " Old Eagle Eye"

Bob Phillips
05-09-2008, 03:46 AM
Well Zack said I had to do my share of the site admin even if it isn't official <g>

Zack Barresse
05-10-2008, 01:50 PM
Well Zack said I had to do my share of the site admin even if it isn't official <g>
YES! VINDICATION!!! :rotlaugh:

Caddyman
05-11-2008, 03:57 PM
Thanx All :friends:

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

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