VBA Clearing Range

[jc0r]

Hi all,

I am trying to run a simple bit of script but keep getting an error and Excel becomes non responsive. I have a 3rd party program inputting data into a sheet every 1 second, and when cell "A2" = "Next" i would like the contents of a certain range to be cleared. I am using Excel 2007.

Many thanks in advance

Private Sub Worksheet_Calculate()

If Sheet3.Cells(2, 1) = "Next" Then
    Range("L4:R6").Select
    Selection.ClearContents
    Range("L8:R500").Select
    Selection.ClearContents
End If

 

[Jonmo1]

It's probably a circular reference type of issue...

Cells calculate - the calculate event triggers
The Calculate event clears contents of cells
the clearing of cells triggers a calculation
The calculation event fires again
Cells are cleared again..Triggering the the calculation event...
Over and over and over again.

You need to disable events, then clear the contents.
Also, you can do it without selecting the cells...

Try

Private Sub Worksheet_Calculate()
If Sheet3.Cells(2, 1) = "Next" Then
    Application.EnableEvents = False
    Range("L4:R6").ClearContents
    Range("L8:R500").ClearContents
    Application.EnableEvents = True
End If

Hope that helps.

 

[jc0r]

Worked a treat, thank you kindly!

 

[Jonmo1]

Glad to help, thanks for the feedback.