There are various specifications and limits for Excel worksheets and workbooks. Therefore it is very important to know the limits when you develope Excel applications. Because then only you can design the application to fulfil the requirements of the user. For an example, if your application need to process several million rows of data, you can design your application in such a way that user can enter the data in several sheets. If you reserve one sheet for data entry when you design the application, then user may not able to input all the data for processing.
Also the specifications and limits are vary from one excel version to another. However maximum number of rows and columns on a worksheet is same for all the latest versions of Excel applications. So the Excel 2013, Excel 2010 and Excel 2007 can have 1,048,576 total rows and 16,384 total columns in one worksheet. However there is no limit for number of worksheets. It will depend on the RAM limit of your device. But row limit and column limit of Excel 2003 is less than above. Microsoft stop supporting for that version as it is outdated.
So now let’s create simple macros to put number 1 to 1,048,576 in column A. Actually you can do this manually like this. Enter number 1 and 2 to two adjacent cells like this.
Then select those two cells.
Now take your mouse pointer to the bottom right corner of the last selected cell. Then cursor will change to + sign. Then click on the left mouse button and drag it until up to the number you want to fill.
Then release the left mouse button.
So you can use this method to fill numbers easily. But even this method will take considerable time if you want to fill numbers up to more than 100,000. But you can do this much quicker using VBA. Place this following code inside a module in a VBA editor and run it.
|
Sub FillNumbers() Dim i As Long For i = 1 To 1048576 ActiveSheet.Range("A" & i) = i Next i End Sub |
It will fill numbers from 1 to 1,048,576 in column A of the active sheet. You can change the for next loop to suit with your requirements. Also you can use similar method to fill a row from 1 to 16384 as well.
