# Golf Spreadsheet - Autoclearing column after input and inserting "most minus"

#### Iswearimnotdumb

##### New Member
I'm working on a spreadsheet for my dad for his golfing group.. Thanks to this forum, we already have a code for automatically shifting the data over a column when new data is entered and we have the formulas in the spreadsheet configured so they do not shift with the data shifting.

What he's wanting is when data is entered into column B, it automatically clears column B after it moves into column E.

Also (not sure if this one is possible), that column D, which is the average of columns E through N, does not decrease more than 2 (value in A1).

Any help or tips would be greatly appreciated!

WCC Dogfight_ses_working.xlsm
ABCDEFGHIJKLMNO
12
2PlayersPointsNet +/-Quota12345678910Total
3Allen, Frankie5175518191922232119151
4Allman, Willie2827301931272730322828279
5Arbaugh, Donnie2327192324172225232422226
Sheet1
Cell Formulas
RangeFormula
D3:D5D3=AVERAGE(INDEX(\$E:\$E,ROW()):INDEX(\$N:\$N,ROW()))
O3:O5O3=SUM(INDEX(\$E:\$E,ROW()):INDEX(\$N:\$N,ROW()))

The code currently in the spreadsheet:

Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.
'
If Not Intersect(Target, Range("B2:B55")) Is Nothing Then
Application.EnableEvents = False
'
Range("E" & Target.Row & ":M" & Target.Row).Cut Range("E" & Target.Row & ":M" & Target.Row).Cells(1).Offset(0, 1)
Range("E" & Target.Row) = Range("B" & Target.Row)
'
Application.EnableEvents = True
End If
End Sub

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### johnnyL

##### Well-known Member
To clear the Column B value after it is moved, place the following before the 'Application.EnableEvents = True' line:
Range("B" & Target.Row) = ""

Replies
3
Views
412
Replies
2
Views
214
Replies
6
Views
231
Replies
4
Views
424
Replies
0
Views
124

1,186,382
Messages
5,957,537
Members
438,311
Latest member
OutLine86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back