Target.count causing an Overflow error

Question

I have Worksheet_SelectionChange function. In the first line I wanted to condition that if more than 1 cell is selected then exit. I wrote:

    If Target.Cells.Count > 1 Then Exit Sub

However, when I select the whole worksheet, I get an error message: "Run time error 6 - Overflow"

It seems like Target.Count can't handle such large numbers ?

What can I do to get around this?

CountLarge is what you need. msdn.microsoft.com/en-us/library/office/… — Tim Williams
– Tim Williams, Commented Sep 10, 2014 at 5:21

Jean-François Corbett · Accepted Answer · 2014-09-10 07:16:24Z

21

Replace Count with CountLarge.

Documentation: http://msdn.microsoft.com/en-us/library/office/ff196838(v=office.15).aspx

The CountLarge property is functionally the same as the Count property, except that the Count property will generate an overflow error if the specified range has more than 2,147,483,647 cells (one less than 2048 columns). The CountLarge property, however, can handle ranges up to the maximum size for a worksheet, which is 17,179,869,184 cells.

answered Sep 10, 2014 at 7:16

community wiki

Jean-François Corbett

Sign up to request clarification or add additional context in comments.

Comments

teylyn · Accepted Answer · 2014-09-10 05:05:25Z

0

Break the check into rows and columns. This way, the count encounters a maximum of "only" 1,048,576 (rows) as opposed to 17,142,120,448 cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Rows.Count > 1 Then Exit Sub
    If Target.Columns.Count > 1 Then Exit Sub
    ' do stuff
End Sub

answered Sep 10, 2014 at 5:05

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

1 Comment

Jean-François Corbett Over a year ago

Good idea — but fails if multiple cells are selected using e.g. Ctrl-click.

Collectives™ on Stack Overflow

Target.count causing an Overflow error

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related