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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Want to provide a bit of contact information in a workbook? A great place to do it (out of sight, but not inaccessible) ...Discover More
If your arrow keys and the Enter key aren't working as you expect them to, the problem could have any number of causes. ...Discover More
One of the many pieces of information that Excel keeps track of is your name. If you want to change your name for Excel's ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.