by Allen Wyatt
(last updated March 7, 2015)
Often when Dave has two workbooks open, he uses Arrange | Horizontal to view them simultaneously. This divides the space evenly between the two workbooks. However, it often happens that Dave would like to see only a few rows of data in one workbook and many rows in the other. He wonders if there is a convenient way to manually size the smaller workbook and have Excel fill the remaining space on the screen with the second workbook.
The manual way of doing this is one that Dave (and most other Excel users) are already familiar with: You arrange the windows horizontally, resize the top window, and then resize the bottom window. In this way you get the windows to be just the way you want in order to do your work.
If you want a more automatic way to resize the windows, you could use a macro to accomplish the task. The following macro relies on you to size the first window the way you want and then it automatically resizes the second window to take up the remaining space below the top window.
Sub UnevenSplit1() Dim Ht0 As Single Dim Ht1 As Single Dim Ht2 As Single Dim Top2 As Single If Windows.Count = 2 Then With Windows(1) Ht1 = .Height .WindowState = xlMaximized Ht0 = .Height End With Top2 = Ht1 + 3 Windows.Arrange ArrangeStyle:=xlHorizontal With Windows(1) .Top = 1 .Height = Ht1 End With With Windows(2) .Top = Top2 .Height = Ht0 - Ht1 - 22 End With Windows(1).Activate End If End Sub
The macro will only resize your workbook windows if you have only two workbooks open. If you have more or less than this, it will appear as if nothing happens.
You can take the automation another step by having the macro resize the top window, as well. The following example ends up with your top window occuplying 25% of the screen and the bottom window occupying 75%.
Sub UnevenSplit() Dim Ht1 As Single Dim Ht2 As Single Dim Ht1a As Single Dim Ht2a As Single Dim Top2 As Single If Windows.Count = 2 Then Windows.Arrange ArrangeStyle:=xlHorizontal Ht1 = Windows(1).Height Ht2 = Windows(2).Height Ht1a = Ht1 / 2 Top2 = Ht1a + 3 Ht2a = Ht2 + Ht1a With Windows(1) .Top = 1 .Height = Ht1a End With With Windows(2) .Top = Top2 .Height = Ht2a End With Windows(1).Activate End If End Sub
You should also be aware that the value of this macro depends, in large part, on what you have visible in your windows. If you have the ribbons at their full vertical depth, that takes a lot of screen space. In fact, it may take so much that even though the top window occupies 25% of the screen height, it may not show any rows of your worksheet because the space is taken up by the ribbon and other interface elements.
There are two possible solutions. The first is to minimize screen elements, such as the ribbon. (Double-click on any ribbon tab in order to minimize the ribbon.) The other solution, of course, is to have the macro use different calculations to determine the final sizes of the windows. (The final window heights are in the variables Ht1a and Ht2a.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13380) applies to Microsoft Excel 2007, 2010, and 2013.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Have you ever opened Excel to find that the window you saw yesterday is not the same as it is today? Sometimes, for ...Discover More
Working on a computer system that has multiple monitors can help increase your productivity. If you want to work with ...Discover More
Want to easily see the location of named ranges in your worksheet? It's easy; all you need to do is use the familiar Zoom ...Discover More
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.