aircraft_model VBA,

In your 'For Each c In [E1:E35000] a cell is selected and then the offset
has to be calculated 35000 times.

If you start in Column J then you avoid the offset calculation 35000 times.
You will only need it if the CaseIs statement is true.

For Each c In [J1:J35000]
Select Case c.Text
Case Is = "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ"
c.Offset(0, -5).Value = c.Offset(0, -5).Value &
IIf(Right(c.Offset(0, -5).Text, 1) < "E", "E", "")
End Select
Next c

That should speed it up a little.

Do While loops are quicker than For Next loops so you could try

Private Sub test()
Dim mycounter As Double
Dim c As Range
Dim d As Range
mycounter = 1

Do While mycounter < 35001
Set c = Range("J" & mycounter)
Select Case c.Text
Case Is = "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ"
Set d = Range("E" & mycounter)
d.Value = d.Value & IIf(Right(d.Text, 1) < "E", "E", "")
End Select
mycounter = mycounter + 1
Loop

End Sub

HTH
Henry


"aircraft_model VBA" wrote in message
...
Presented below is a macro called cleandata that runs at snailspace!!.

I would like 2 changes to this slow macro:

1. Change the reference of the worksheet from the name (in this case =
new) to simply active worksheet, so that it is not name specific.

2. Speed it up so that it works faster.


Sub Cleandata()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("new")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "K").Value = "0" Then
.Rows(r).Delete
End If
Next
End With
For Each c In [E1:E35000]
Select Case c.Offset(0, 5).Text
Case Is = "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ"
c.Value = c.Value & IIf(Right(c.Text, 1) < "E", "E", "")
End Select
Next c
Application.ScreenUpdating = True
End Sub

If you have some suggested improvements not mentioned above, please feel
free to make them to the modified code.

As always, thanks in advance to the great excel speedsters!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!