power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Overcoming Excel Row and Column Number Limitations

ShareThis

When you begin writing entire data sets from VBA code to your worksheet, rather than summarized data only, you can quickly bump into Excel's upper limits on the number of available columns and rows.

The following table shows the maximum possible number of rows and columns in Excel versions 2003 (and earlier), and Excel 2007:

  Rows Columns
Excel 2003 65,536 256
Excel 2007 1,048,576 16,834

So, if you're simply writing your data to your worksheet in one row per iteration, your models will be limited to ~65,000 iterations in Excel 2003, and ~1,000,000 iterations in Excel 2007.

It would be possible to write VBA code which would insert new worksheets to store the additional data.  However, having data stored across multiple worksheets makes analysis cumbersome.

The workaround we'll demonstrate here is to use the range .Offset property discussed earlier in combination with some "For" loops to output the data into multiple columns, as seen in the LotsaData example screenshot below:

Let's look at the VBA code behind this model.

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.