0

Any thoughts on what is killing me performance-wise here, and what could I do instead? It's taking a good two minutes to run the below code block. The worksheet called "BSEV" has about 54000 rows, for reference. I'm guessing that it's either RemoveDuplicates or the row-deleting loop. What could I do instead?

With AllSubj
    .Range(.Cells(1, 1), .Cells(BSEVLRow - 1, 2)).Value = _
        BSEV.Range(BSEV.Cells(BSEVFRow, 3), BSEV.Cells(BSEVLRow, 4)).Value

    .Range(.Cells(1, 1), .Cells(BSEVLRow - 1, 2)).RemoveDuplicates 1, xlNo

    AllSubjLRow = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    DCSubjLRow = AllSubjLRow

    For i = AllSubjLRow To 1 Step -1
        If .Cells(i, 2).Value <> "Active in Core" Then
            .Rows(i).EntireRow.Delete
        End If
    Next i

    AllSubjLRow = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    AllSubjArr() = .Range(.Cells(1, 1), .Cells(AllSubjLRow, 1)).Value
End With
Michael Smith
  • 72
  • 8
  • 3
    The row-deleting loop... see the answer [here](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba). – BigBen Jun 05 '20 at 15:06
  • Thoughts on an alternative? If I just loop through BSEV and use an IF statement to narrow down what I want, I'm going to end up looping through it several thousand times. Even if I'm looping through an array, I'm concerned that that will be slower. Am I wrong? – Michael Smith Jun 05 '20 at 15:07
  • See the answer I linked to and use `Union`... or maybe `Range.AutoFilter` and delete visible cells. – BigBen Jun 05 '20 at 15:08
  • `Application.ScreenUpdating = False` is also a must. (I realize you've only provided a code snippet, but in case you had forgotten it, it slows down execution considerably). – Miqi180 Jun 05 '20 at 15:20
  • @BigBen, thanks, that was exactly what I had in mind. Couldn't quite conceive of the solution myself, although seeing how simple it is, I'm embarrassed to admit. @ miqi180, yeah I have that going. I've completely avoided any formulas in the whole workbook, so no need for application.calculate :). – Michael Smith Jun 05 '20 at 15:25
  • @MichaelSmith Something else to consider is *can you sort the data*? Excel is slightly faster at deleting a contiguous group of rows than it is deleting multi-range areas (i.e. `Rows("2:5").Delete` is faster than `Union(Rows(2),Rows("4:5"),Rows(9)).Delete`) - although this usually only becomes noticeable when you are dealing with *lots* of data and/or formulae - so sorting on Column 2, and *then* deleting the rows may be the fastest option – Chronocidal Jun 05 '20 at 15:51
  • 1
    I haven't given it a try yet, so let me see what the performance bump looks like. I need the data in the order that they're exported, and I'm not sure that I can re-sort it easily since one of the columns to sort by is Like "Visit 4," so it doesn't play nicely with A to Z. – Michael Smith Jun 05 '20 at 15:54
  • 1
    @MichaelSmith You can always try adding a counter column with `With UsedRange.Resize(, 1).Offset(0, UsedRange.Columns.Count): .Formula = "=Row()": .Calculate: .Value = .Value: End With` to re-sort it when you're done, and delete that afterwards - but, as you say, see which permutation of options gives appropriate performance – Chronocidal Jun 05 '20 at 16:00
  • Ohhhhh that is **too** good. What a sweet trick. – Michael Smith Jun 05 '20 at 16:06

1 Answers1

1

The link provided by BigBen provides a solution for columns, and within the comments, you can find an identical solution for rows.

For Loop not fully cycling in excel VBA

Michael Smith
  • 72
  • 8