Overflow Error While Selecting The Entire Worksheet

[modytrane]

I have following code in the worksheet and every time I select the entire worksheet it generates an "Overflow" error.
Just curious to know why and if anything needs to be changed in the code to allow user to select the entire sheet.

Please Login or Register  to view this content.

Thanks for any suggestions.

modytrane.

[Firefly2012]

Hi

2007 has 1mill+ rows by thousands of columns - if you select the entire sheet you exceed the maximum value of the Long datatype returned by the Count function. 2007 comes with CountLarge to cater for this:

Please Login or Register  to view this content.

[OnErrorGoto0]

It is due to the number of cells in the 'big grid'. You can use

Please Login or Register  to view this content.

instead and it should be OK.

[snb]

But I think this suffices:

Please Login or Register  to view this content.

[OnErrorGoto0]

It might suffice, but it is not the same code since the original one exits if more than one cell is selected. If that was not the intention, then you may be correct.
I would also use an If...ElseIf rather than two separate If statements that cannot both be satisfied.

[snb]

@onerrorgoto0

but it is not the same code since the original one exits if more than one cell is selected

My alternative is just doing that.

[OnErrorGoto0]

@snb,
No, it is not. If you selected the whole of column B for example, it would call your second macro, would it not?

[snb]

@onerrorgoto0

You are right..

maybe:

Please Login or Register  to view this content.

[OnErrorGoto0]

That could still give you an overflow (though more unlikely!), so we are back to square one.

[modytrane]

Thanks for all the replies.
CountLarge seems to work.

I have used the .Count<>1 in many other applications, so it's just a matter of habit. I know it probably won't matter much in this case. In other cases I needed it to make sure user doesn't select multiple cells and copy data or formulas.

Thanks for the answers and discussion. Learned something today.

modytrane.

[modytrane]

Thanks for all the replies.
CountLarge seems to work.

I have used the .Count<>1 in many other applications, so it's just a matter of habit. I know it probably won't matter much in this case. In other cases I needed it to make sure user doesn't select multiple cells and copy data or formulas.

Thanks for the answers and discussion. Learned something today.

modytrane.