could make code is fast for 3500 rows

[Abdo]

Hi
first thanks mr.Alex Blakenburg for this code and help me in earlier thread function IsEmpty doesn't work for Workbook_SheetSelectionChange .
second I search for way to make this code is fast
my data will increase from time to another .so my data can reach 3500 rows. by the way I note the code is slight slow even if the data are about 300 rows.
can anybody make this code is fast ?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngInputs As Range
    Dim rngRows As Variant
    
    Set rngInputs = Intersect(Target, Columns("C:D"))

    If Not rngInputs Is Nothing Then
        Application.EnableEvents = False
        
        For Each rngRows In rngInputs.Rows
            If rngRows.Row <> 1 Then
                If IsEmpty(Cells(rngRows.Row, "C")) and IsEmpty(Cells(rngRows.Row, "D")) Then
                    Cells(rngRows.Row, "E") = 0
            Else
                    Cells(rngRows.Row, "E") = Cells(rngRows.Row, "D") - Cells(rngRows.Row, "C")
                End If
            End If
        Next rngRows
        
        Application.EnableEvents = True
    End If

End Sub

 

[bebo021999]

This code trigger change in column C:D, then D-C
Each time the code run, it only work with cells those change only.
I don't know why the speed is slow, in this case?
I have tested until row 5000 and things seem OK

 

[Abdo]

Hi bebo
thanks for inform me , maybe from the PC

 

[Georgiboy]

If you have a lot of formula on the worksheet then this could speed things up a little, switch off auto calculation:

Sub test()
    With Application
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    ' Code here
    
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

 

[Anthony47]

The macro could take a long time to complete in case that "blocks of cells" in the target area are changed at the same time; for example clearing a column or a range.

To deal with this situation we might use array, so that I/O to/from the worksheet be minimized; for example:

Private Sub Worksheet_Change(ByVal Target As Range)
'https://www.mrexcel.com/board/threads/could-make-code-is-fast-for-3500-rows.1211646/
    Dim rngInputs As Range
    Dim rngRows As Variant
Dim rArr, eArr, rrCnt As Long
Dim myTim As Single, tSize As Long, rc0R As Long
    
Set rngInputs = Intersect(Target, Range("C2:D10000"), Me.UsedRange.EntireRow)

    If Not rngInputs Is Nothing Then
'        tSize = rngInputs.Count
'        myTim = Timer
        rrCnt = rngInputs.Rows.Count
        Set rngInputs = Cells(rngInputs.Cells(1, 1).Row, "C").Resize(rrCnt + 1, 2)
        Application.EnableEvents = False
        rArr = rngInputs.Value
        eArr = Cells(rngInputs.Cells(1, 1).Row, "E").Resize(rrCnt + 1, 1).Value
        For I = 1 To rrCnt
            If IsEmpty(rArr(I, 1)) And IsEmpty(rArr(I, 2)) Then
                eArr(I, 1) = 0
            Else
                eArr(I, 1) = rArr(I, 2) - rArr(I, 1)
            End If
        Next I
        rngInputs.Cells(1, 3).Resize(rrCnt, 1) = eArr
'        Debug.Print tSize, Format(Timer - myTim, "0.000")
        Application.EnableEvents = True
    End If
End Sub

Try...

 

[Abdo]

@Anthony47 thanks
somtimes your code doesn't make differnce about the speed , but somtimes makes difference .

 

[Anthony47]

You should be more explicit; like:
-if I do this then that
-if I do this other then that other
-if etc etc
Olso you should clarify which is the timescale you talk about

 

[Abdo]

@Anthony47 there is no odd thing what I do . based on the code just select cells for two columns and clear them ,as to the others cells are not selected does contain zero I no know if this can make it slow somtimes. what I look for just loop through the selected cells and ignore all cells in columns C,D contain zero .

 

[Anthony47]

I still didn't understand if the macros you work with are slow when you change 1-2-3 cells or 100-1000 of them; or if you are talking of delay in the range of 0.1-1-10-100 seconds

You wrote:

somtimes your code doesn't make differnce about the speed , but somtimes makes difference

Was you able to determine in which cases it improved the speed (please specify from xx to yy) and in which cases it didn't improve?