by Allen Wyatt
(last updated May 8, 2019)
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Hate to take your fingers off the keyboard? Here's how you can move from worksheet to worksheet without touching the mouse.Discover More
Add-ins for Excel, such as the Analysis ToolPak, are stored in files on your hard drive that can be deleted. If you ...Discover More
Excel tries to anticipate what you want to type into a cell, particularly when it comes to entering formulas. Here are ...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.