Md. Abdur Rahim Rasel
When we have a very large datasheet, sometimes we need to set the limit for visible areas in Excel. Restricting rows and columns manually may take some extra time for you. But using shortcuts and VBA macros for this purpose can really help save some of your time. In this article, you will learn how to set a limit for visible area in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Ways to Set Limit for Visible Area in Excel
Consider the following dataset. Let’s use this dataset to demonstrate what actions to take when a limit is set for visible areas. We will use the Hide & Unhide command, Properties Window command, and VBA code as well as set the limit for visible areas in Excel. Here’s an overview of the dataset for today’s task.
Method 1: Using Hide & Unhide Command to Set Limit for Rows and Columns
We will apply the Hide & Unhide command to set the limit for visible areas in Excel. First of all, we use this command to restrict rows. Hence, we will restrict columns.
1.1 Limit Rows to Restrict Visible Area
Now, we will set limit rows to restrict visible areas. From our dataset, we can easily do this task. Let’s follow the instructions below to learn!
Steps:
- Firstly, select your data range where you will set limit rows to restrict visible areas. From our dataset, we will select B4 to E14. Hence, from your Home tab, go to,
- As a result, you will be able to set limit rows to restrict visible areas which have been given in the below screenshot.
Read More: How to Limit Data Range in Excel Chart (3 Handy Ways)
1.2 Limit Columns to Restrict Visible Area
Now, we will learn how to set limit columns to restrict visible areas in Excel. Let’s follow the instructions below to learn!
Steps:
- Set limit columns to restrict visible areas, firstly, select C7 to D13. After that, from your Home tab, go to,
- Finally, you will be able to set limit columns to restrict visible areas which have been given in the below screenshot.
Read More: How to Set Limit in Excel Cell (5 Simple Ways)
Method 2: Utilize Properties Window Command to Set Visible Area Limit in Excel
In this method, we will apply the Properties Window command under the Developer tab to set the limit for visible areas. Let’s follow the instructions below to learn!
Steps:
- First of all, open a Microsoft Visual Basic for Applications, to do that, firstly, from your Developer tab, go to,
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Limit Visible Area will instantly appear. From that window, we select the View Further, from the View tab, go to,
- After that, type $A$1:$E$19 in the ScrollArea typing box.
- As a result, you will be able to set limits for visible areas.
- To understand this method clearly, have a look at the below GIF.
Read More: How to Limit Scroll Area in Excel (3 Easy Ways)
Method 3: Run Excel VBA Code to Set Limit for Visible Area in Excel
Last but not least, I’ll show you how to limit cells to restrict visible areas in Excel by using a simple VBA code. It’s beneficial for some particular moments. From our dataset, we will limit cells to restrict visible areas in Excel. Let’s follow the instructions below to learn!
Step 1:
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Limit Visible Area will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Step 2:
- Hence, the Limit Visible Area module pops up. In the Limit Visible Area module, write down the below VBA code.
Private Sub Limit_Visible_Area()
ActiveSheet.ScrollArea = "B4:E19"
End Sub
- Further, run the VBA To do that, go to,
- Afterward, running the VBA Code, go back to your worksheet and you will be able to set the limit for visible areas which has been given in the below GIF.
👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously. You can open the Microsoft Visual Basic Applications window by using the
 ALT + L + V  shortcut as well.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
Read More: How to Limit Sheet Size in Excel (3 Suitable Ways)
Conclusion
I hope all of the suitable methods mentioned above to set limits for the visible areas will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
