Faster Way to Substract 2 Columns?

[EnigmaMatter]

Range("M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Dim CEL As Range
     
    For Each CEL In Selection
    If CEL.Value <> "" Then
        If Range("O" & CEL.Row).Value <> "" Then
        CEL.Value = CEL.Value - Range("O" & CEL.Row).Value
        End If
    End If
    Next CEL
    
    MsgBox ("Done")

Is there a faster, better way?

[PCI]

Perhaps

Sub TestPCI()
Dim WkRg As Range
Dim WkTb1, WkTb2
Dim LastRow As Long
Dim I  As Long

    LastRow = Range("M" & Rows.Count).End(xlUp).Row
    Set WkRg = Range("M2:M" & LastRow)
    WkTb1 = WkRg
    Set WkRg = Range("O2:O" & LastRow)
    WkTb2 = WkRg
    For I = 1 To UBound(WkTb1)
        If ((WkTb1(I, 1) <> 0) And (WkTb2(I, 1) <> 0)) Then WkTb1(I, 1) = WkTb1(I, 1) - WkTb2(I, 1)
    Next
    Range("M2").Resize(UBound(WkTb1, 1), 1) = WkTb1

End Sub

[EnigmaMatter]

Curiosity, why does this work so much better than my script?

[PCI]

Because access to the spreadsheet is limited to the minimum:
All data is read and put in memory in one shot: Wktb1 = WkRg, WkTb2 = WkRg
Treatment is done in memory
Complete result is written in one shot in the cells

Range("M2").Resize(UBound(WkTb1, 1), 1) = WkTb1

[jindon]

try

Sub test()
    With Range("m2", Range("m" & Rows.Count).End(xlUp))
        .Value = Evaluate(.Address & "-o2:o" & .Cells(.Count).Row)
    End With
End Sub

[YasserKhalil]

Mr. Jindon What if I want the results to be in Q2:Q & LastRow?

[EnigmaMatter]

PCI,

It bugs on:

WkTb1(I, 1) = WkTb1(I, 1) - WkTb2(I, 1)

in line:

If ((WkTb1(I, 1) <> 0) And (WkTb2(I, 1) <> 0)) Then WkTb1(I, 1) = WkTb1(I, 1) - WkTb2(I, 1)

[PCI]

Can you send a short sample of your data, with mine it was OK
Perhaps try depending of your data

If ((WkTb1(I, 1) <> "") And (WkTb2(I, 1) <> "")) Then WkTb1(I, 1) = WkTb1(I, 1) - WkTb2(I, 1)

[EnigmaMatter]

It looks identical except it is in a table...but yours works in a table too...

[EnigmaMatter]

PCI,

Your substitute line worked! Thanks!

Why did it work?

[PCI]

I have the "feeling" there is not only numbers in your data, that's why it is always better to have a sample ...!

[YasserKhalil]

Mr. Jindon What if I want the results to be in Q2:Q & LastRow?
Thanks advanced

[jindon]

    With Range("m2", Range("m" & Rows.Count).End(xlUp))
        Range("q2:q" & .Cells(.Count).Row).Value = Evaluate(.Address & "-o2:o" & .Cells(.Count).Row)
    End With

[YasserKhalil]

Thanks a lot Mr. Jindon