How to Limit the Number of Rows in Excel (3 Methods)

An Excel worksheet has 1048576 rows and 16384 columns.  In the dataset, you will see sales information about a grocery store. The number of sales items in the store is not that much. We’ll use this dataset to explain the methods for limiting the number of rows in Excel.

limit number of rows in excel

Method 1 – Hiding Rows to Limit Number of Rows in Excel

  • Hiding Rows:
    • The easiest way to limit rows in an Excel worksheet is by hiding them.
    • Follow these steps:
      • Select an empty row after your dataset (e.g., if you want to keep up to the 14th row, select the 15th row).

      • Pres sCTRL+SHIFT+Downward key to select all remaining empty rows.
      • Right-click on the selected cells and choose Hide.

  • Alternatively, you can use the same command from the Cells group on the Home tab: Format >> Hide & Unhide >> Hide Rows.

limit number of rows in excel

  • After this command, all empty rows after the 14th will be hidden and not available for use.

limit number of rows in excel

Read More: How to Handle More Than 1048576 Rows in Excel

Method 2 – Set Scroll Area in Properties Control (Temporary)

  • You can temporarily limit the number of rows by disabling the scroll area using the Developer Property for an Excel sheet.
  • Keep in mind that this setting won’t persist if you close and reopen the workbook.
  • Follow these steps:
    • Go to Developer >> Property.

    • Type the range you want to enable for scrolling (e.g., $1:$15).

limit number of rows in excel

    • Close the Properties window.
    • The scrolling area outside this range of rows will be disabled, limiting the usable rows.


Method 3 – Use VBA Code (Permanent)

  • To permanently limit the number of rows, you can use VBA code.
  • Follow these steps:
    • Go to the Developer tab and select Visual Basic.

limit number of rows in excel

    • Open the VBA sheet from the VBAProject window and activate the desired worksheet.

    • Type the statement: ScrollArea = “A1:XFD15” (adjust the range as needed).
Private Sub Worksheet_Activate(ByVal Target As Range)
Me.ScrollArea = "A1:XFD15"
End Sub
  • This code restricts the scrolling area to the specified rows.
    • Here, we activate the worksheet for Private Sub Procedure and set the desired scrolling area. In this case, I set the range A1:XFD15 for scrolling. This means it’s impossible to select any cell outside this range and thus we can limit the number of rows to operate.

limit number of rows in excel

    • Finally, press CTRL+S to save the file and go back to your sheet.


To ensure that the code pasted in the sheet module works correctly, follow these steps:

  • Activate the Worksheet:
    • Click on the drop-down icon next to the sheet name (usually located at the bottom left corner of the Excel window).
    • Select the desired worksheet from the list.
    • Now you can paste the code into the sheet module, and it should function as expected.

Remember, activating the correct worksheet is essential for proper code execution.

Read More: How to Set the End of an Excel Spreadsheet

Download Practice Workbook

You can download the practice workbook from here:


Related Articles

<< Go Back to Row and Column Limit | Rows and Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF