Detecting sheet change, clearing the sheet giving an overflow

Question

I have a Worksheet_Change event, currently at the Sheet Module Level. The problem is that I want to be able to clear this sheet at times. However, when I clear my sheet I get an overflow:

Private Sub Worksheet_Change(ByVal Target As Range)
    'This is the line causing the problem because clearing the whole sheet causes the count to be massive
    If Target.Count = 1 Then
        If Target = Range("A4") Then
            If InStr(LCase(Target.Value), "loaded") <> 0 Then
                Range("A5").FormulaArray = "=My_Function(R[-1]C)"
            End If
        End If
    End If
End Sub

I am trying to achieve the following:

I press a button and the sheet is cleared (clears existing array formula data), I then paste in a formula to the sheet and call the formula. The formula returns data back to the excel cache and changes the cell containing this formula (A4) to a string saying "loaded". When I detect a cell change with value "loaded" I then do the equivalent on Ctrl + Shift + Enter on an array formula function below, to display the data.

score 0 · Accepted Answer · edited May 23 '17 at 11:48

I believe you are using xl2007+?

The Target.Cells.Count is a Long value and hence when you select the entire worksheet the .Count is too small to hold the result.

Replace line

If Target.Count = 1 Then

with

If Target.Cells.CountLarge = 1 Then

You might also want to see this since you are using Worksheet_Change

EDIT:

Two other things

1)

You can also replace this line

If Target = Range("A4") Then

with

If Not Intersect(Target, Range("A4")) Is Nothing Then

2)

This line

If InStr(LCase(Target.Value), "loaded") <> 0 Then

can also be written as

If InStr(1, Target.Value, "loaded", vbTextCompare) Then

Detecting sheet change, clearing the sheet giving an overflow

1 Answers1

Linked