|To simply the part of the code that divides the values by 1000, try this:|
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 5 To 9, 11 To 15
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Target / 1000
Application.EnableEvents = True
To simplify the part that is causing the "average" issue, I would do it with a spreadsheet formula, not by eliminating the values via VBA. Looping through your ranges, cell by cell, is going to take a long time.
A simple solution to not include 0's in an average is to use this formula:
=SUM(A1:A100) / (COUNT(A1:A100) - COUNTIF(A1:A100,"=0"))
This seems to work for the situation where you are deleting a value as you mentioned in Response # 2.
Other methods, to handle other situations, can be found here:
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.