Making a Named Range Non-Scrollable

by Allen Wyatt
(last updated September 12, 2017)

4

Brian has a worksheet divided into two named-row ranges. He wonders how he can make the upper range fixed (permanently visible) while the lower range is scrollable.

It may be possible, but the answer depends on a lot of variables. First, let's assume that the two named ranges are Range1 and Range2 and that Range1 is physically above Range2. In this case the answer is rather easy: you can use Excel's built-in tools to keep Range1 in view at all times. For instance, you could use the "freeze panes" tool in this way:

  1. Display Range1 at the top of the worksheet.
  2. Select the left-most cell just below the range.
  3. Display the View tab of the ribbon
  4. Click the Freeze Panes tool.
  5. Click Freeze Panes.

Excel dutifully displays freezes the rows and columns above and to the left of the cell so they are aways displayed.

You could, if you prefer, split the window. You do this by following these steps:

  1. Display Range1 at the top of the worksheet.
  2. Select the left-most cell just below the range.
  3. Display the View tab of the ribbon
  4. Click the Split tool.

Excel divides the screen into either two or four views of the same worksheet.

Both panes and splits result in portions of your screen that are tied to each other when it comes to scrolling. For instance, if you scroll left or right, then both vertical portions of the screen (above the freeze line or the split line) scroll synchronously. If you don't want this scrolling to occur—you really want the top pane/split to stay frozen independent of the bottom one—then Excel doesn't provide any way to facilitate such a need.

Further, if you want Range2 to appear as the upper range, even when it is physically below Range1, then the closest you can come is to split the window (don't use the "freeze panes" tool) and display Range2 in the upper split. You can then, in the lower split, display Range1. Of course, scrolling in one split will still affect what is viewed in the other split.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12995) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the Status Bar

When developing a macro, you may want to display on the status bar what the macro is doing. Here's how to use this important ...

Discover More

Highlighting the Rows of Selected Cells

If you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, but ...

Discover More

Deleting All Headers and Footers

Headers and footers add a finishing touch to documents, but sometimes they can be bothersome. You may need to remove them all ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Inserting and Copying Rows

Want an easy way to insert a new row in a worksheet and copy everything from the row above? (You end up with two identical ...

Discover More

Spreading Out a Table

If someone sends you a worksheet that has lots of data in it, you might want to "spread out" the data so you can have some ...

Discover More

Linked Combo Boxes

Want to add a great way to interact with your worksheet users? Try adding combo boxes that can modify the information shown ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2014-03-12 08:25:32

Yvan Loranger

You wrote "If you don't want this scrolling to occur—you really want the top pane/split to stay frozen independent of the bottom one—then Excel doesn't provide any way to facilitate such a need."

I beg to differ. Use menu View / New Window. Then scroll independently to your hearts content. You can shrink top window to show only 1 row if you wish.


2014-03-10 09:11:38

Jennifer Thomas

Bryan, if you can determine the new freeze location reliably, you could run a macro with these lines included:

Range("XX").Select ActiveWindow.FreezePanes = True

But determining what XX should be is the trick; maybe you can use some formatting condition or known value+locaton from the odbc link to get that location and then pass that as a variable to your freeze panes macro.

Hope that points you in the right direction!


2014-03-08 10:21:08

bryan

This would work for a fixed range1,
I had assumed this was from a variable range1. How would you do it for a variable?
If range1 was budget info that came from a odbc data link. And range2 was monthly spending, the number of rows in range 1 may change depending on how many items were in the budget for that month.

Is there a way to have it auto adjust freeze size?


2014-03-08 08:19:31

Brian Hershman

For my exact problem, the first "Freeze Panes" solution works perfectly. I do not know why I did not think of it myself: I use Freeze Panes all the time, but usually only to freeze Row $1 and column $A.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.