Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Number of rows in Excel 2013

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.

Contact Form

Name

Email *

Message *

Popular Posts