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

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
610
Office Version
2016
Platform
Windows
You need some "event" to trigger it. In the example we've already done, the macro does the deleting too, so can check the number of job sites prior to deletion, and store the totals. But if rows are to be deleted manually, the totals won't be updated.

Same applies to this new example - how will the macro know that source data is about to be deleted?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
Oooo, i think i get what you mean. So, there isn't a basic macro or formula that i can do that will just save the current totals? like if the sheet i am wanting to do says a total of 10, but the source sheet auto deletes and goes down to 3, there isn't a way to make it keep saying 10, like prevent it from subtracting, only adding. Or would i have to use a similar macro on a different sheet?
 

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
610
Office Version
2016
Platform
Windows
A formula will just keep calculating. The original macro stored the number of deleted records in a seperate set of cells, adding to that number each time a record was auto-deleted by the macro. We then amended the formulae to add in the running totals from the deleted records cells.

What the original macro won't solve is where you may need to delete a record manually. This is because if the record isn't being deleted by the macro, the macro isn't updating the set of cells containing the number of deleted records.

There is a way (with more macros) that you can get around this! This involves two more macros:
  • A macro that automatically runs whenever the selected cell on the worksheet changes. This stores the values of the three cells in column M in a different column (I'll use O).
  • A macro that automatically runs whenever a cell value changes on the worksheet (i.e. it doesn't run if you're just moving around the worksheet). This looks at the "new" values of the three cells in column M and compares them to the "old" values which were stored in column O by the previous macro. If the values have gone down (i.e. a record has been blanked/deleted), it will increase the count of deleted records in column O, which adds back into the formulae in column M.
You would also need to amend the original macro, so that when it auto-deletes, it doesn't also trigger the "cell value change" macro above, as this may potentially duplicate the adjustments.

There is a potential issue with this - you'll need to consider whether it makes it too risky! The new macros run on any amendment to the sheet, not just a deletion! So if a new record is input as Job Site 1, then the user realises this was wrong and overtypes it as Job Site 2, the macro will assume that a Job Site 1 record is being deleted, and adjust the totals accordingly. Also bear in mind that there will be macros running in the background all the time - if your spreadsheet is huge, this may make it run a little bit slower.

But if you do want to go ahead...:

First step is to adjust the original macro. On the row immediately above Do put:
Code:
Application.EnableEvents = False
On the row immediately below Loop put:
Code:
Application.EnableEvents = True
These two rows stop other automated macros from running while the original macro is doing its deletions and adjustments

Next step is the new macros. These need to be put in the part of the VBA editor for the sheet containing the records, which I think from your previous posts is
Sheet38(Job Data) -
they should not be put with the original macro in ThisWorkbook:
Code:
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 M cells in column O
    Range("O10").Value = Range("M10").Value
    Range("O11").Value = Range("M11").Value
    Range("O12").Value = Range("M12").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 M by comparing them with the old values stored in column O by the other macro.
    DifferenceM10 = Range("O10").Value - Range("M10").Value
    DifferenceM11 = Range("O11").Value - Range("M11").Value
    DifferenceM12 = Range("O12").Value - Range("M12").Value
    
    'If the cell value has reduced, the total for deleted records needs to be increased by the difference
    If DifferenceM10 > 0 Then Range("N10").Value = Range("N10").Value + DifferenceM10
    If DifferenceM11 > 0 Then Range("N11").Value = Range("N11").Value + DifferenceM11
    If DifferenceM12 > 0 Then Range("N12").Value = Range("N12").Value + DifferenceM12
    
    'Store the current values of column M cells in column O
    Range("O10").Value = Range("M10").Value
    Range("O11").Value = Range("M11").Value
    Range("O12").Value = Range("M12").Value
    
    'Re-enable other automatic macros
    Application.EnableEvents = True
End Sub
I've done these macros based on the Job Site sheet, as I know what the cell ranges used are. But you should be able to adapt this for other sheets if you want.

Final thought - one of your previous posts mentioned deleting columns! Remember that all the cell ranges are written into the macros, so if you start deleting columns on the sheet, figures may appear in places that you're not expecting them to!

 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
Thanks, i'll look and tinker with it. Largely the columns don't get changed on the main A - k. I recently made some changes but they have been the same for years and needed to be updated. SO when we do have to add things like drop down data etc we do it off to the side. So, i should be ok. I'll give it a shot. I know i had another thread with trying to get some help with sumproducts, i need to check in there, was having an issue with getting 1 formula to work perfectly But its mostly there.
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
Haven't gotten to play with it yet, been a little busy but i am gonna try over the next few days.
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
Ok, finally got around to playing with it, but have an issue i think. But not sure if i missed something your you typed N10 instead on M10?

So i have made some adjustments to the sheet, A - K is still all the same, and i was able to modify your original macro to compensate so that is all good. So the current column i have the MX being tracked is in column W, 4 thru 15. I created a similar setup as we did for the other macro where as it deletes it goes into another column, in this case is AA 4 thru 15 and adjusts as it deletes. But i can quite get this one to work the same way. Am i just missing something?
 

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
610
Office Version
2016
Platform
Windows
Columns M and N in the new macro do the same as the did in the original one - N is the number of deleted rows, and M is the number of rows still existing for that job site plus the figure in N. In the original macro, I could count the number of rows that would be deleted before they actually were, and increase the value in N accordingly. The new macro can't do that, as it reacts to a change that has just happened. That's why I needed column O - that is regularly updated with the value of the formula in M (rather than the formula itself). When something on the spreadsheet changes that affects the result in column M, the value in column O will have remained the same. The difference between M and O will be the number of rows deleted, and N can be increased by that.

You've mentioned that you can't get what you've done to work the same way. What's happening, and are you using the new or old macro?
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
Hmm, think i need to make adjustments. As the original macro deletes the rows by date, i can see the maintenance tallys decreasing, but not auto adding to the other columns to track its overall totals.
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
I am playing with it, but still kicking my butt a little. Sorry for all the questions, trying to learn some VBA as i go along with you. What would column N represent? I am guessing that i may need to incorporate that as well?

I thought i had it working at first, but then i noticed that it was tracking every time i moved the cursor to a new cell, so i was off on something haha.

Not sure if i just have something out of place at the moment?

I can try and copy in how i have altered it, might be easy to see the mistake that way?
 

PlumGr8

Board Regular
Joined
Nov 23, 2017
Messages
72
this is how i currently have it typed in,

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
DifferenceM12 = Range("AA7").Value - Range("W7").Value
DifferenceM12 = Range("AA8").Value - Range("W8").Value
DifferenceM12 = Range("AA9").Value - Range("W9").Value
DifferenceM12 = Range("AA10").Value - Range("W10").Value
DifferenceM12 = Range("AA11").Value - Range("W11").Value
DifferenceM12 = Range("AA12").Value - Range("W12").Value
DifferenceM12 = Range("AA13").Value - Range("W13").Value
DifferenceM12 = Range("AA14").Value - Range("W14").Value
DifferenceM12 = 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

'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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,002
Messages
5,484,134
Members
407,432
Latest member
Suldarion

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top