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!
