Check the range is not the whole sheet?

Question

I have this function that tries to determine when a particular cell value changes. The problem is that if the user selects the entire spreadsheet and press delete, I get an overflow in my check that the range is only one cell:

Public Sub Worksheet_Change(ByVal Target As Range) 'Overflow can occur here if range = whole spreadsheet If Not IsError(Int(Target.Cells.Count)) Then If Target.Cells.Count = 1 Then If Target.Cells.Row = 4 And Target.Cells.Column = 1 Then Sheets("X").Cells(5, 1).Calculate End If End If End If End Sub 

Is there a more elegant way that I can get this code to work only when changing one specific cell value? (without overflow, problems when cleaning the entire sheet, etc.)?

Answer 1

I assume you are in Excel 2007+, as the number of rows and columns has increased dramatically in these versions. You may be able to check your luck to make sure that the number of rows and columns = 1, as these maximum values ​​will be much less than the product of two (i.e. the number of cells):

 If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then 

Answer 2

Use CountLarge instead of Count

 Private Sub Worksheet_SelectionChange(ByVal target As Range) If target.Cells.CountLarge > 1 Then Exit Sub 'Code... End Sub 

See: MSDN Range.CountLarge Property (Excel)