Programmatically getting the maximum allowed number of columns and rows of a Sheet

Question

In a sick Excel (I use Office 10) VBA macro, i need to trap the case where the user managed to create a sheet with the maximum number of columns or rows by accident.

I don´t remember how that happens, nor would I care; but most of us have seen Excel sheets that had an active sheet size of 16384 cells in width and/or 1048576 cells in height -- even though there were only a handful of nonempty cells on the sheet.

I simply want to detect this situation in VBA code.

A sheet´s Rows.Count and Columns.Count of course return the figures, and I could check if they equal 16384 or 1048576, respectively.

However, those limits are version-dependent.

So my question is:

How can I get the maximum limit for the number of rows and columns in a given Excel worksheet in VBA without coding version-dependent if-statements? There might be a constant for each limit value, but I yet failed to find it.

Note I am not looking for the size of Worksheet.UsedRange. I want to determine if UsedRange has been extended to the whole available "sheetspace", which usually happens only by accident, but -- it happens, and I want to detect that situations.

I am also not looking for the limits per Excel version. (This is easily googleable.) I don´t want to hardcode those values and version numbers.

Answer 1

The easiest way to accomplish this is by comparing your UsedRange's Row and Column count to your Sheet's Row and Column count.

For example:

If ActiveSheet.UsedRange.Rows.Count = ActiveSheet.Rows.Count Then
   ... However you want to deal with this scenario ...
End If

And similarly with column count.