Up next in 10
Limit Scrolling Dynamically - Auto-Adjust Scroll Limits in Excel with VBA
2K views
Feb 20, 2025
How to limit the scroll area in Excel while still allowing data expansion? In this tutorial, you'll learn how to use VBA to dynamically limit the scroll area based on your data range. Watch how the scroll area expands as you enter new data and shrinks when you delete data—all automatically! 00:00 Introduction 00:36 Revisiting Limit Scrolling with VBA 01:34 Create Macro to Enable/Disable Scrolling Limit 03:35 Use Form Buttons to Control Scrolling Limit 04:25 Advanced Technique to Dynamically Limit Scroll Area with VBA Based on Data Range
View Video Transcript
0:00
hi and welcome in a previous tutorial we
0:02
explored two methods to limit scrolling
0:05
in Excel the first method hides extra
0:07
rows and columns while the second method
0:09
uses VBA to restrict scrolling to a
0:12
specific range in this tutorial we'll
0:14
take it a step further and show you how
0:16
to dynamically control the scroll bar
0:18
based on your data let's start by
0:20
enabling the developer tab if it's not
0:22
already visible on the ribbon right
0:24
click anywhere on the ribbon choose
0:26
customize ribbon and then check the
0:28
developer option in the Excel options
0:29
dialog box once enabled the developer
0:32
tab will appear on the ribbon let's
0:34
first revisit how we used VBA in our
0:36
previous tutorial to set a fixed scroll
0:38
area open the VBA editor by pressing alt
0:42
f11 in the project Explorer find the
0:44
worksheet where you want to limit the
0:45
scroll area and doubleclick its name to
0:48
open the code window insert the
0:50
worksheet uncore activate event it runs
0:52
automatically whenever the worksheet is
0:54
selected now enter the code is shown on
0:57
screen this code restricts scrolling to
0:59
the range A1 to H20 the Mi keyword
1:02
refers to the current worksheet and
1:04
scroll area defines the area where users
1:06
can navigate to test the code either
1:09
click the play button in the VBA editor
1:11
or switch to a different sheet and then
1:13
return to the one with the code when the
1:14
sheet is activated the worksheet
1:16
underscore activate event triggers
1:18
executing the code and limiting
1:20
scrolling to the specified range if you
1:22
want to remove the scroll restriction
1:24
simply set scroll area to an empty
1:26
string setting scroll area to an empty
1:29
string removed any limitations restoring
1:32
full scrolling access now let's move on
1:34
to a more Dynamic way to limit scrolling
1:37
we'll start with a simple and efficient
1:38
approach that requires minimal code open
1:41
the VBA editor again and remove all the
1:43
code we wrote earlier for a fresh start
1:46
next insert a new module by clicking the
1:48
insert menu and selecting module this
1:51
creates a new blank editor window and
1:53
you'll see a new module named module one
1:55
appear in the project Explorer now let's
1:58
start writing our code first create a
2:00
sub routine named limit scroll and
2:02
insert the code shown on screen the used
2:04
Range property refers to the range of
2:06
cells that contain data starting from
2:08
the first used cell to the last by
2:11
assigning used Range address to scroll
2:13
area we dynamically restrict scrolling
2:15
to only the area where data exists this
2:18
way as data grows or shrinks the
2:21
scrollable area updates accordingly
2:23
click the play button to run the sub
2:25
routine then close the VBA editor now
2:27
try scrolling the window won't move move
2:30
beyond the data range try selecting
2:32
empty cells outside the data area you
2:34
can't the scroll is now limited to the
2:37
used Range to remove this restriction
2:39
let's create another sub routine named
2:42
reset scroll insert the code shown on
2:44
screen as we mentioned earlier setting
2:46
scroll area to an empty string removes
2:49
all scroll limitations restoring full
2:51
navigation click the play button to run
2:53
the reset scroll sub routine then close
2:56
the VBA editor now try scrolling the
2:59
Excel Window or selecting empty cells
3:01
you'll see that the reset scroll sub
3:03
routine has successfully removed the
3:05
scrolling restrictions allowing
3:07
unrestricted movement across the
3:08
worksheet now you can easily limit or
3:11
remove scrolling restrictions using the
3:13
macros dialogue box run the limit scroll
3:16
to apply the scroll limit test it out
3:18
you'll notice that you can no longer
3:20
scroll the window or select any empty
3:22
cells outside the data range now open
3:25
the macros dialogue box again and this
3:27
time run reset scroll you you'll see
3:30
that the Restriction is removed now you
3:32
can scroll freely and select any cell
3:34
without any
3:35
limitations to make this process more
3:37
convenient and interactive let's add
3:39
buttons to limit or remove scrolling
3:42
restrictions go to the developer tab
3:44
click insert in the controls group and
3:46
choose a button from form controls draw
3:48
the button on the sheet and when the
3:50
assigned macro dialogue box appears
3:52
select limit scroll and click okay
3:54
rename the button to limit scrolling now
3:57
whenever you press this button scrolling
3:59
will be restricted to the data range try
4:02
it and you'll see that you can't scroll
4:04
or select any empty
4:09
cells now insert another button and
4:12
assign at the reset scroll macro
4:14
clicking this button will remove the
4:16
scroll restriction restoring normal
4:18
navigation with just two buttons and
4:21
minimal code you can easily toggle
4:23
scrolling
4:24
limits but we're not stopping here if
4:27
you prefer an even more Dynamic approach
4:29
with without using buttons where Excel
4:31
automatically detects when you need to
4:33
enter more data delete these buttons and
4:35
open the VBA editor
4:38
again let's modify the limit scroll sub
4:41
routine by updating its parameter as
4:43
shown on the screen this change allows
4:45
the sub routine to receive a copy of a
4:47
reference to a worksheet and modify its
4:49
scroll Area Property replace active
4:52
sheet with SH in a sub routine instead
4:54
of directly modifying the active sheet
4:56
the sub routine now takes a worksheet
4:59
object sh as a parameter this makes it
5:02
more flexible as we can call limit
5:04
scroll for any worksheet by passing it
5:06
as an argument for example if we want to
5:09
limit the scroll area on sheet one
5:11
dynamically our goal is to ensure that
5:13
the scrollable range expands as new data
5:15
is added in contracts when data is
5:18
deleted to achieve this we use the
5:20
worksheet under score change event as
5:22
shown on the screen the worksheet and
5:24
score change event triggers
5:26
automatically whenever a change occurs
5:27
in the worksheet whether data is
5:29
inserted modified or deleted when this
5:32
happens the event calls the limit scroll
5:35
sub routine passing active sheet as the
5:37
argument the limit scroll sub routine
5:40
then updates the scroll Area Property
5:42
dynamically based on the used Range of
5:44
the sheet ensuring that users can only
5:46
scroll within the active data range this
5:48
makes the scroll area adapt to changes
5:51
in real time without manual intervention
5:54
as you can see on the screen we can
5:55
freely scroll through the worksheet now
5:58
let's enter a value in into any cell and
6:00
press Center when we do this the
6:03
worksheet inter score change event is
6:04
triggered which calls the limit scroll
6:07
sub routine the sub routine then
6:09
restricts the scroll area to the current
6:10
data range preventing us from scrolling
6:13
or selecting any cells Beyond this range
6:16
however while the code successfully
6:18
Limits The Scroll area it also prevents
6:20
us from adding new data beyond the
6:22
current range meaning we can't expand
6:24
our data beyond the used Range without
6:26
manually running the reset scroll macro
6:28
to make this process says fully Dynamic
6:30
so the scroll area updates automatically
6:33
without needing to run the reset scroll
6:35
macro let's open the VBA editor again
6:37
and modify the limit scroll sub routine
6:40
as shown on the screen first we declare
6:43
three variables last row and last call
6:45
to store the last row and last column of
6:48
the used Range last cell to hold the
6:50
bottom right cell reference for defining
6:52
the scroll area next we determine the
6:54
last used row and add two to its value
6:58
then we do the same for the last fast
6:59
use column this ensures that the scroll
7:01
area extends beyond the current data
7:03
range providing extra space for new
7:06
entries we then set last cell as the
7:08
bottom right boundary of the scroll area
7:11
finally we update the scroll area
7:13
property of a worksheet limiting it from
7:15
cell a one to last cell this dynamically
7:18
restricts the scrolling area while still
7:20
allowing some flexibility for data
7:22
expansion now whenever data is added or
7:25
removed the limit scroll sub rutine
7:27
ensures that the scroll area always
7:29
always leaves room for two additional
7:31
rows and columns this means we don't
7:33
need to manually reset the scroll area
7:35
every time we add new data the advantage
7:37
of this approach is that when we enter
7:39
data in a blank row or column the scroll
7:42
area automatically expands after the
7:44
entry ensuring a seamless experience now
7:47
go back to the Excel sheet and enter
7:49
some data you'll notice that two extra
7:52
rows and columns are available for data
7:53
entry when you enter data in any of
7:56
these cells the used Range automatically
7:58
extends allowing you to continue adding
8:00
more data as you keep entering data the
8:03
scroll area expands dynamically this way
8:06
you get the benefit of both limited
8:08
scrolling and flexible data entry
8:10
ensuring that the scroll area adjusts
8:12
automatically while still maintaining
8:14
control over where users can navigate
8:17
now try removing data from the last few
8:19
columns and rows as you delete the data
8:21
you'll notice the scroll area shrinking
8:24
dynamically Contracting to match the
8:26
updated used Range this means the
8:28
worksheet always keeps the scroll area
8:30
optimized based on the actual data
8:32
present to visualize how the scroll area
8:35
is working let's highlight it with a
8:37
light yellow background first go back to
8:39
the VBA editor and enter this line at
8:42
the top of the limit scroll sub routine
8:44
this will reset the entire sheets
8:45
background color to its default usually
8:48
white next before the last line add
8:50
another code line as shown on the screen
8:52
this will highlight the scrolling range
8:54
with a light yellow background while
8:56
keeping the rest of the worksheet in its
8:57
default State now go back to excel try
9:01
adding and removing data and see how the
9:03
highlighted area expands and contracts
9:06
dynamically as the scroll area adjusts
9:08
these two lines of code are only for
9:10
demonstration purposes they help us
9:12
visually understand how our code is
9:14
working once you're confident about the
9:16
functionality you can remove these lines
9:18
to prevent any unnecessary formatting in
9:21
your workbook
9:34
to test if the scroll limit persists
9:36
after reopening the workbook first save
9:39
and close your workbook then reopen it
9:41
now try scrolling you'll notice that you
9:44
can freely navigate the entire sheet
9:46
next enter some data in any cell you'll
9:49
see that there are no restrictions
9:51
meaning our code has not executed after
9:53
reopening the workbook however as soon
9:55
as you modify or enter new data the
9:58
scroll limit is instantly applied this
10:00
happens because we're using the
10:02
worksheet under score chain event which
10:03
only triggers when we add edit or delete
10:06
data inside the sheet to ensure the
10:08
scroll limit is applied automatically
10:11
when the workbook opens we need to run
10:13
the VBA code as soon as the workbook is
10:15
loaded this can be achieved using the
10:17
workbook _ open Event the workbook open
10:20
Event runs automatically whenever the
10:23
workbook is opened making it the perfect
10:25
solution to apply our limit scroll sub
10:27
routine without waiting for user input
10:30
let's open the VBA editor double click
10:32
the this workbook module in the project
10:34
Explorer and in the code window insert
10:36
the workbook in scope an event inside
10:38
this event call the limit scroll sub
10:40
routine passing sheet one or the sheet
10:43
you want to limit scroll now save and
10:46
reopen the workbook this time you'll see
10:48
that the scroll limit is already applied
10:50
as soon as the workbook loads now you've
10:52
seen how we can dynamically control the
10:54
scroll area in Excel while still
10:56
allowing data expansion the VBA code
10:59
automatically adjust the scroll limit as
11:01
we add or remove data ensuring a balance
11:04
between navigation control and
11:06
flexibility if you found this tutorial
11:08
helpful please like share and subscribe
11:10
for more Excel tips and automation
11:11
tricks don't forget to hit the
11:13
notification Bell so you never miss an
11:15
update thanks for watching and see you
11:17
in the next video