CODEWITHSUNDEEP
excelvba
geeta
geeta

Reputation: 7

Faster calculation for 500,000 rows

I need your help to find Excel vba code. I have input rows as RNC name and CellID. In Concatenate column I'm using concatenate formula to combine RNC name and CellID. It is through a recorded macro which is taking very very long time to complete for copying/paste special 3-500,000 cells as values.

RNC Name    CELLID  Concatenate     Cell Name   Count
RNCBESI05   10621   RNCBESI05_10621 ALJRMG_1    31
RNCBESI05   10621   RNCBESI05_10621 ALJRMG_1    31
RNCBESI05   10621   RNCBESI05_10621 ALJRMG_1    31

Could you please kindly suggest how it will be faster to calculate 1/2M cells?

Recorded Code:

Sheets("Neighbor").Select
Range("C2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],""_"",RC[-1])"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Cell!C[-3]:C,4,0)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
    Range("C2:E2").Select
    Selection.Copy
    Range("B2").Select
    Selection.End(xlDown).Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Selection.End(xlUp).Select

Upvotes: 0

Views: 1064

Answers (1)

user4039065
user4039065

Reputation:

Capturing the cell range values to a variant array and processing in memory should improve the calculation time.

Sub three_ops()
    Dim v As Long, vVALs As Variant, vCELs As Variant

    With Worksheets("Cell")
        Debug.Print Intersect(.Columns("A:D"), .UsedRange).Address(0, 0)
        vCELs = Intersect(.Columns("A:D"), .UsedRange).Value2
    End With

    With Worksheets("Neighbor")
        vVALs = Intersect(.Columns("A:D"), .UsedRange).Value2
        For v = LBound(vVALs, 1) + 1 To UBound(vVALs, 1)
            vVALs(v, 3) = Join(Array(vVALs(v, 1), vVALs(v, 2)), Chr(95))
            If Not IsError(Application.Match(vVALs(v, 3), Application.Index(vCELs, 0, 1), 0)) Then
                vVALs(v, 4) = Application.VLookup(vVALs(v, 3), vCELs, 4, 0)
            End If
        Next v
        With Intersect(.Columns("A:D"), .UsedRange)
            .Value = vVALs
            With .Resize(.Rows.Count - 1, 1).Offset(1, 4)
                .Formula = "=countif(" & .Offset(0, -1).Address(1, 0) & ", d2)"
                .Value = .Value2
            End With
        End With
    End With
End Sub

I've never had much success with the COUNTIF function and variant arrays. You may want to look at Is there a faster CountIf to speed up that operation.

Upvotes: 2

Related Questions