Macro to Clear Cells when specific cell is reached

[Chris Turner]

I have a worksheet that has data entered into cells A1 to B30. The cells C1 to C30 contains formulas to calculate the difference between two data points e.g A1 to B1, A2 to B2, etc. down the columns A30 to B30

I am trying to create a macro that will clear cells A1 to B30 when data is entered into cell B30. After clearing the cells, the cursor should move back up to cell A1 to allow me to start the data entry cycle again. I would also like to only allow data to be entered into cells A1 to B30 and stop any other cells in the worksheet from being used.

I have been working with this macro to clear the contents but would like to automate the process instead of using a button to activate the macro:

Sub sbClearCellsOnlyData()
Range("A1:C30").ClearContents
End Sub

Appreciate any help. Thanks

 

[Chihiro]

I'm a bit confused. You are to enter info in A1:B30 only.
But you are clearing A1:C30. How is formula populated in C1:C30 range? Is it done via code? Or are you manually populating it?

I'd recommend uploading sample workbook with detail of your current process (where data is transferred after entry etc).

 

[Chris Turner]

Thanks for the quick reply. I have attached a sample workbook to demonstrate how the current worksheet operates. I inserted some sample data in columns N and 0. This sample data would not be there in the actual worksheet.

 

[Chihiro]

Try below, in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Count([A1:B30]) = 60 Then
    [A1:B30].ClearContents
End If
End Sub

However, do note that this will clear "Undo" operation cache.
As well, currently it clears content as soon as you finish entering last cell in A1:B30 range. If you want to do something after you update last cell in range...

I'd recommend using another layer of logic/trigger to process the code.

Edit: added sample workbook

 

[Chris Turner]

Chihiro, Thank you for the macro code. It works perfectly. I am not sure how you would use another layer of logic to trigger the process. I did think about trying to see if I could add a delay of 2 or 3 seconds after entering the value into cell B30, which is enough time to see the result in C30 before it clears all the cells.

 

[Chris Turner]

Chihiro, I thought about what you said in your reply and I came up with these additions to the macro. I added a delay and a return to cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Count([A1:B30]) = 60 Then
Application.Wait (Now + TimeValue("0:00:03"))
[A1:B30].ClearContents
Range("A1").Select
End If
End Sub

I truly appreciate your help with this macro. Thank you so much!

 

[Marc L]

I would also like to only allow data to be entered into cells A1 to B30

A must read in VBA inner help : ScrollArea worksheet property …
​