Clever Geek Handbook

Target.count causing overflow error

I have a function Worksheet_SelectionChange

. In the first line, I would like to set that if more than 1 cell is selected, exit. I wrote:

    If Target.Cells.Count > 1 Then Exit Sub

However, when I select the entire worksheet, the error message appears: "Runtime Error 6 - Overflow"

It seems that Target.Count

can not handle such large numbers?

What can I do to get around this?

+3


source to share


2 answers


Replace Count

with CountLarge

.

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



The property is CountLarge

functionally the same as the property Count

, except that the property Count

generates an overflow error if the specified range has more than 2,147,483,647 cells (one less than 2048 columns). However, the property CountLarge

can handle ranges up to the maximum sheet size, which is 17,179,869,184 cells.

+10


source


Split check into rows and columns. Thus, the counter encounters a maximum of "only" 1,048,576 (rows) as opposed to 17,142,120,488 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
0


source






More articles:


All Articles