Is there a way to keep the current total when resetting/deleting cells?

[PlumGr8]

So i have a formula for counting even that have occurred at certain sites. Like different job sites doing different events and a simple one that basically says

Job site 1 did this
Job site 2 did that

we delete everything older then 2 months as the year progresses, just to keep file sizes smaller. Is there a way to create a formula that will maintain the overall total though? So, that say there was 450 events, and we delete 2 months worth, that the total wont decrease and will always maintain and increase its value? I don't think there is, but this site is awesome for help so couldn't hurt to ask.

 

[Trevor_S]

No problem, its useful to see the macro in full. It appears that you're using column W for the main formulae, AG for the numbers of deleted records, which are added into the formulae in W, and AA to store the previous values of the formulae. The macro can then tell that a deletion has taken place when the result of a formula in W is lower than the value stored on the same row of AA.

 

[Trevor_S]

The issue is with the DifferenceM... variables. They appear in two blocks of the macro. In the first block their value is set as being the difference between W and AA. In the second block, if the difference is greater than 0, records have been deleted, and that number is added to AG (the number of deleted records).

In my original macro we had three of these variables - DifferenceM10, DifferenceM11 and DifferenceM12. This is because we were only monitoring changes for three rows. You're now looking at twelve rows, so you need twelve different variables. However in the first section of your macro that uses these variables, after the third line you repeat the same variable name for the other 9 lines. Each calculation is overwriting the effect of the previous one. And in the second section you still only have three lines - you need 9 more, so you can update the AG values for the last 9 rows.

You can call the variables what you like, provided that the names are the same in both sections of the macro. However, to perhaps make it easier to see what is going on, I'd suggest using DifferenceW4, DifferenceW5, and so on until DifferenceW15. Do this in both sections of the macro that refer to the Difference... variables, remembering that you'll need an extra nine lines in the second section.

 

[Yongle]

Try this in a TEST worksheet, with the code below in that sheet's SHEET module
The sub is triggered if anything changes in column W below row 3
Instead of repeating for every row
- use the pattern that AA4 is 4 columns to right of W4 and AG4 is 10 columns to right of W4
- use a common variable ( Diff )to calculate the difference on any row

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Diff, AA As Range, AG As Range
    If Target.Row < 4 Then Exit Sub               [I][COLOR=#006400]  'values to be considered start in row 4[/COLOR][/I]
    On Error GoTo TheEnd                           [COLOR=#006400][I] 'ensures events is enabled even if macro fails[/I][/COLOR]
    If Not Intersect(Columns("W"), Target) Is Nothing Then
        Application.EnableEvents = False
        Set AA = Target.Offset(, [COLOR=#800080]4[/COLOR])
        Set AG = Target.Offset(, [COLOR=#800080]10[/COLOR])
        Diff = AA - Target
        If Diff > 0 Then                           [COLOR=#006400][I] '[/I][/COLOR][COLOR=#ff0000][I]***[/I][/COLOR][COLOR=#006400][I] see note[/I][/COLOR]
            AG = AG + Diff
            AA = Target
        End If
    End If
TheEnd:
Application.EnableEvents = True
End Sub

*** If diff > 0 Then only doing anything if difference is positive
for all changes to be reflected:

If diff [COLOR=#ff0000]<>[/COLOR] 0 Then

 

[Yongle]

Here range variable W is added when VBA confirms that the amended cell is in column W
- possibly makes it easier to read and understand than using Target..

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim diff, AA As Range, AG As Range, W As Range
    
    If Target.Row < 4 Then Exit Sub
    On Error GoTo TheEnd
    If Not Intersect(Columns("W"), Target) Is Nothing Then
        Application.EnableEvents = False
        Set [COLOR=#ff0000]W[/COLOR] = [COLOR=#ff0000]Target[/COLOR]
        Set AA = [COLOR=#ff0000]W[/COLOR].Offset(, 4)
        Set AG = [COLOR=#ff0000]W[/COLOR].Offset(, 10)
        diff = AA - Target
        If diff > 0 Then
            AG = AG + diff
            AA = [COLOR=#ff0000]W[/COLOR]
        End If
    End If
TheEnd:
Application.EnableEvents = True
End Sub

 

[PlumGr8]

Duhhh haha. I totally brainfarted on that one. Guess as i was going through and adjusting it, i totally skipped over that middle section as i was copying down the list of values. Sorry i totally should have caught that one. Thanks for being patient, going to go play with it again and see if i can get her going.

 

[PlumGr8]

I'll give yours a shot as well Yongle. Thanks for the info as well.

 

[PlumGr8]

So Trevor here i am this is my code now

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Active cell on worksheet has changed. Stop other automatic macros running whilst this one is making adjustments.
Application.EnableEvents = False

'Store the current values of column W cells in column AA
Range("AA4").Value = Range("W4").Value
Range("AA5").Value = Range("W5").Value
Range("AA6").Value = Range("W6").Value
Range("AA7").Value = Range("W7").Value
Range("AA8").Value = Range("W8").Value
Range("AA9").Value = Range("W9").Value
Range("AA10").Value = Range("W10").Value
Range("AA11").Value = Range("W11").Value
Range("AA12").Value = Range("W12").Value
Range("AA13").Value = Range("W13").Value
Range("AA14").Value = Range("W14").Value
Range("AA15").Value = Range("W15").Value

'Re-enable other automatic macros
Application.EnableEvents = True

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

'A change has taken place on the worksheet. Stop other automatic macros running whilst this one is making adjustments.
Application.EnableEvents = False
'Calculate any change in the values in column W by comparing them with the old values stored in column AA by the other macro.
DifferenceM10 = Range("AA4").Value - Range("W4").Value
DifferenceM11 = Range("AA5").Value - Range("W5").Value
DifferenceM12 = Range("AA6").Value - Range("W6").Value
DifferenceM13 = Range("AA7").Value - Range("W7").Value
DifferenceM14 = Range("AA8").Value - Range("W8").Value
DifferenceM15 = Range("AA9").Value - Range("W9").Value
DifferenceM16 = Range("AA10").Value - Range("W10").Value
DifferenceM17 = Range("AA11").Value - Range("W11").Value
DifferenceM18 = Range("AA12").Value - Range("W12").Value
DifferenceM19 = Range("AA13").Value - Range("W13").Value
DifferenceM20 = Range("AA14").Value - Range("W14").Value
DifferenceM21 = Range("AA15").Value - Range("W15").Value

'If the cell value has reduced, the total for deleted records needs to be increased by the difference
If DifferenceM10 > 0 Then Range("AG4").Value = Range("AG4").Value + DifferenceM10
If DifferenceM11 > 0 Then Range("AG5").Value = Range("AG5").Value + DifferenceM11
If DifferenceM12 > 0 Then Range("AG6").Value = Range("AG6").Value + DifferenceM12
If DifferenceM13 > 0 Then Range("AG7").Value = Range("AG7").Value + DifferenceM13
If DifferenceM14 > 0 Then Range("AG8").Value = Range("AG8").Value + DifferenceM14
If DifferenceM15 > 0 Then Range("AG9").Value = Range("AG9").Value + DifferenceM15
If DifferenceM16 > 0 Then Range("AG10").Value = Range("AG10").Value + DifferenceM16
If DifferenceM17 > 0 Then Range("AG11").Value = Range("AG11").Value + DifferenceM17
If DifferenceM18 > 0 Then Range("AG12").Value = Range("AG12").Value + DifferenceM18
If DifferenceM19 > 0 Then Range("AG13").Value = Range("AG13").Value + DifferenceM19
If DifferenceM20 > 0 Then Range("AG14").Value = Range("AG14").Value + DifferenceM20
If DifferenceM21 > 0 Then Range("AG15").Value = Range("AG15").Value + DifferenceM21

'Store the current values of column W cells in column AA
Range("AA4").Value = Range("W4").Value
Range("AA5").Value = Range("W5").Value
Range("AA6").Value = Range("W6").Value
Range("AA7").Value = Range("W7").Value
Range("AA8").Value = Range("W8").Value
Range("AA9").Value = Range("W9").Value
Range("AA10").Value = Range("W10").Value
Range("AA11").Value = Range("W11").Value
Range("AA12").Value = Range("W12").Value
Range("AA13").Value = Range("W13").Value
Range("AA14").Value = Range("W14").Value
Range("AA15").Value = Range("W15").Value

'Re-enable other automatic macros
Application.EnableEvents = True
End Sub


and as they delete from column W (as our original macro does its job deleting the old rows by date), i can see the values in column AA increasing. But, if i click any place on the sheet, the values in AA go back to zero? I am also still trying to get what do the DifferenceM10 - 20 values do? As i have no data in that column? I am also guessing that like the original macro i have to modify the formula to add in the values from AA.

I did try the other posters suggestions as well, my only concern with them is if in the future they add in cells for some reason in column W, it might interfer. So, trying to get yours dialed in as it will be basically "set" for those specific cells.

 

[Yongle]

my only concern with them is if in the future they add in cells for some reason in column W, it might interfere

Are you concerned about rows being inserted & deleted?
- the code will still continue to work

To stop at row 15 (like your code), insert this BEFORE On Error GoTo TheEnd

If Target.Row > 15 Then Exit Sub

If your concern is something different let me know
thanks

 

[PlumGr8]

i can try that too. So with his macro, is it basically saying if it detects any change in the values to column W that it will add the to the column in AA?

 

[PlumGr8]

Are you concerned about rows being inserted & deleted?
- the code will still continue to work

To stop at row 15 (like your code), insert this BEFORE On Error GoTo TheEnd

If Target.Row > 15 Then Exit Sub

If your concern is something different let me know
thanks

Tried his macro, but not getting anything to transfer from W to AA.

Also is it referring over to AG for rows being deleted over all? Will that cause a conflict as in those cells i am not tracking for every row deleted in those. Unlike the original macro, i just need to make sure the total in these cells are saved/added to to continue the total

The other thing it is doing different, is unlike the original Macro that maintains and saves the number count, the ones in these cells just keep going back to zero as the rows are auto deleted?