Programmatically get the maximum number of columns and rows in a worksheet

Question

In a sick Excel (I use Office 10) VBA macro, I need to catch the case when the user was able to create a sheet with the maximum number of columns or rows by accident .

I do not remember how this happens, and I do not care; but most of us saw Excel sheets that had an active sheet size of 16384 cells in width and / or 1048576 cells in height - although there were only a few non-empty cells on the sheet.

I just want to detect this situation in VBA code.

The sheets Rows.Count and Columns.Count , of course, return numbers, and I can check if they match 16384 or 1048576 .

However, these restrictions are version dependent.

So my question is:

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

Note. I am not looking for the size of Worksheet.UsedRange . I want to determine if UsedRange extended to all available " UsedRange space", which usually happens only by accident, but - this happens, and I want to detect these situations.

I am also not looking for restrictions on the version of Excel. (This is easily googleable.) I do not want to hardcode these values ​​and version numbers.

Answer 1

The easiest way to achieve this is to compare the number of rows and columns UsedRange with your rows of rows and columns.

For instance:

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

And similarly to the column count.