more efficient than a loop?

[TinaP]

Hi folks!

I have a list of records, 12000+ records long, where I must remove any records with a dollar field under 3000. I've automated the importing of the data as well as most of the other tasks related to generating the report using VBA. Currently, I'm looping through every record and deleting records that are under 3000, then sorting. It takes more than 5 minutes just for this segment of code. Can anyone help me make the code more efficient?

Private Sub Remove_Trans_Under_3000()
Dim Cell As Range

  Application.Calculation = xlManual
  Application.ScreenUpdating = False
  Range("L2") = "=SUMPRODUCT(--(DateRange=A2),--(PurchaserRange=G2),--(VoidRange<>""Y""),AmtRange)"
  Range("HelperRange").FillDown
  Calculate
  For Each Cell In Range("HelperRange")
    If Cell < 3000 Then Cell.EntireRow.Clear
  Next Cell
  Range("L1").EntireColumn.Delete
  Cells.Select
  Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

 

[starl]

Duplicate. It's the same project - since you've been working on it, just update the original q so experts can see the whole picture.

http://www.mrexcel.com/board2/viewtopic.php?t=296913