Dividing the Screen Unevenly between Two Workbooks

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 occupying 25% of the screen and the bottom window occupying 75%.

Sub UnevenSplit()
    Dim sQtr As Single

    If Windows.Count = 2 Then
        Windows.Arrange ArrangeStyle:=xlHorizontal
        sQtr = Windows(1).Height / 2
        Windows(1).Top = 1
        Windows(1).Height = sQtr
        Windows(2).Top = sQtr + 3
        Windows(2).Height = sQtr * 3
        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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13380) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.