Efficient code - Cycle throught rows

[dispelthemyth]

Hi

I generally analyse data for stuff that relates to my area and delete anything that does not belong to me


The code i use is below

(I use Excel 2007)

<Code>
Sub StartAnalysis()
3
If ActiveCell.Value = "DIVISION" Then GoTo 1
If ActiveCell.Value = "FJSNSD" Then GoTo 2
If ActiveCell.Value = "FJSION" Then GoTo 2

ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
GoTo 3

2
ActiveCell.Offset(-1, 0).Select
GoTo 3

1

End Sub

<End Code>

I generally speed this up by using the below to disable calculations and screen updating

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

But in some books i have used, it says the GoTo option is a bad/slow method for cycling through rows like this. I was wondering what the alternatives were


Thanks for any and all help


Graeme

 

[VoG]

Try like this

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If IsError(Application.Match(Range("A" & i), Array("DIVISION", "FJSNSD", "FJSION"), 0)) Then Rows(i).Delete
Next i
End Sub

 

[Richard Schollar]

Hi Graeme

There are multiple ways you could achieve this - for example if you wanted to loop you could perhaps use:

Sub Test()
Dim rng As Range
Set rng = Activecell
Do
   If rng.Value = "DIVISION" Then Exit Sub
   If Not (rng.Value = "FJSNSD" Or rng.Value = "FJSION") Then rng.EntireRow.Delete
   Set rng = rng.Offset(-1)
Loop While rng.Row>2
End Sub

Other options would include Autofilter (likely to be faster if you have lots of data).

 

[dispelthemyth]

Thanks, your help is much appreciated

On the 2nd code, there is an object error on the line

Set rng = rng.Offset(-1)


Im going to look into the Autofiler option, as its for 10,000+ lines so that would end up deing the fastest route

thanks again, Graeme