I have seen some SO users run into an issue when trying to use some variation of Cells.Count
; the VBA code throws an overflow error in some cases.
For reference, see comments on this answer:
I think this will work, but I get an "overflow" error and it points me to the code "If Master.Cells.SpecialCells(xlCellTypeVisible).Count > 0 Then" --- it seems like it's not filtering for anything in particular – user1556069
and this answer:
Is this onyl working (and Cells.Count didnt work) because the latter used an integer, 16 bits, max value of 65,536 and the whole spreadsheet returned a numbr greater? – fast_code
I'm assuming that somewhere behind the scenes VBA is trying to coerce the cell count to a small Integer (16-bit) or Long integer (32-bit). The cell count of an Excel 2007 worksheet would overflow both of those datatypes. Unfortunately I can't isolate it right now because I don't have a copy of Excel 2007 handy and cannot actually reproduce your error. – mwolfe02
Trying to understand this, I tried to reproduce myself and got an overflow when trying to assign Cells.Count
as an Integer. This makes sense as the value is too large for the Integer data type.
Using the code below in both Excel 2003 and 2010, I was given a numeric result when trying to assign as a Long or Variant.
Option Explicit
Sub testInteger()
Dim i As Integer
i = Cells.Count 'Overflow
Debug.Print i 'Doesn't get this far...
End Sub
Sub testLong()
Dim l As Long
l = Cells.Count
Debug.Print l 'Prints 16777216 in both versions
End Sub
Sub testVariant()
Dim v As Variant
v = Cells.Count
Debug.Print v 'Prints 16777216 in both versions
End Sub
As you can see in my comments, the Cells.Count
value is 16777216
(which is correct for 2003), but it is the same for both versions, and that doesn't make sense to me. To quote mwolfe02 from one of the above-linked answers:
Excel 2007 worksheets have 1,048,576 rows and 16,384 columns for a total of 17,179,869,184 cells.
Which tells me the value printed in 2010 should be at least (I believe it should really be the same) 17,179,869,184
.
So why does this number not print correctly/why is the 2003 value returned in 2010?
When calculating such large numbers use .Countlarge
property.
For example
Sub CellsCount()
Dim l As Double
l = ActiveSheet.Cells.CountLarge
Debug.Print l
End Sub
Also never use Cells.Count
or Cells.CountLarge
without specifying the worksheet object. This is to ensure that we don't get incorrect count/error in compatibility mode. Similarly never use Rows.Count
. Always use ws.Rows.Count
. This is the most common error people make while trying to find the last row in excel. For example
This
lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
and
lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
might not give you the same results always.
I would also recommend further reading of this.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With