Find the Maximum Row/Column with VBA

Friday, 10 August 2012 13:27 Last Updated on Sunday, 12 August 2012 10:52 Written by Steve Powell

Introduction

Now that Excel 2007/2010 support up to 1 million rows we can’t hard-code the column/row limits anymore and oftentimes you’ll need to know what the Workbook supports. One reason that comes to mind is that you want to quickly find the last cell in the first column that has a value in it. So you use the End method of the Range starting at the bottom row. Another might be to determine if you have enough room to place the result of a very large query that has more than 256 columns and would need the bigger limits of 2007/2010.

There isn’t a single property on the Workbook that tells you what the limit is but it’s easy to figure out. I’ll give you a couple of simple functions to use.

How to do it and how not to do it

Check the File Name

Can we just check the file name? For instance if the workbook ends with .xlsx/.xlsb/.xlsm, can we say the row limit is 1048576 and the column limit 16384? You would think so but try saving a .xls file in Excel 2010 as the new format (.xlsx)  and then check the last row. It will still be 65536 until you re-open the file so this method would not be reliable.

Use the Columns/Rows property and CountLarge

This is a pretty simple and reliable method. Just check the Count property of the Rows property of the sheet, or the Columns property:

Public Function MaxRows(Book As Workbook) As Long
    MaxRows = Book.Sheets(1).Rows.CountLarge
End Function

Public Function MaxColumns(Book As Workbook) As Long
    MaxColumns = Book.Sheets(1).Columns.CountLarge
End Function

Both functions are very similar. I use CountLarge because Count returns an Integer and in Excel 2007+ the row limit is 1048576 which would overflow an Integer. I know the columns won’t exceed the limit of Integer so Count would be sufficient. The benefit of using CountLarge here is just to be consistent and ensure it’s compatible with future Excel versions that may have more than 16k columns (if there ever is such a thing).