Dividing the Screen Unevenly between Two Workbooks

by Allen Wyatt
(last updated March 7, 2015)

9

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.

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

Understanding the Start Menu

When you want to start using Windows for real, you'll typically need to go through the Start menu to do it. This tip ...

Discover More

Setting the Return Address Used in Word

When you create envelopes with Word, it normally displays a return address by default. If you can't get Word to retain the ...

Discover More

Using Data Validation

Want to control what users put into a cell? It's easy to do using a feature called data validation, as described in this tip.

Discover More

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!

More ExcelTips (ribbon)

Disappearing Status Bar

Ever had your Excel status bar disappear unexpectedly? Here's some ideas on why this may be happening.

Discover More

Maintaining the Active Cell

Move from one worksheet to another, and Excel selects whatever cell was last used in the worksheet you are selecting. If you ...

Discover More

Disabled Page Setup Tools

It can be frustrating if you expect to use some of Excel's tools normally available on the ribbon but those tools are ...

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 four less than 9?

2015-03-13 09:23:55

Glenn Case

Thanks to Jerry for the tip on workspace!


2015-03-10 08:32:25

George Reasoner

As Khushnood wrote, the problem is that invisible windows are counted. My solution is close to his, but accounts for the case that the visible windows are not 1 and 2.

Sub UnevenSplit1()
Dim Ht0 As Single
Dim Ht1 As Single
Dim Ht2 As Single
Dim Top2 As Single
Dim VisibleWindowCount As Integer
Dim VisibleWindows(2) As Integer


VisibleWindowCount = 0
For Each win In Windows
If win.Visible Then
VisibleWindowCount = VisibleWindowCount + 1
If VisibleWindowCount > 2 Then Exit Sub
VisibleWindows(VisibleWindowCount) = win.Index
End If
Next

If VisibleWindowCount = 2 Then
With Windows(VisibleWindows(1))
Ht1 = .Height
.WindowState = xlMaximized
Ht0 = .Height
End With
Top2 = Ht1 + 3
Windows.Arrange ArrangeStyle:=xlHorizontal
With Windows(VisibleWindows(1))
.Top = 1
.Height = Ht1
End With
With Windows(VisibleWindows(2))
.Top = Top2
.Height = Ht0 - Ht1 - 22
End With
Windows(VisibleWindows(1)).Activate
End If
End Sub


2015-03-09 05:46:19

Dave S

Bill
Only reason I can think of for macros not running at all is if your macro security setting has been set to 'Disable all macros without notification'. From File ribbon select Options - Trust Center, then Trust Center Settings... - Macro Settings, and check which setting has been selected.


2015-03-09 03:36:50

Dave

Many thanks to all the contributors for this tip. Lots of food for thought there. I am particularly fond of little macros - you should see my QAT! - so I can feel a new addition coming along. :o))

Regards and thanks,
Dave


2015-03-08 08:22:09

MarkSF

A pity they took the Save Workspace functionality away in Excel 2013


2015-03-07 20:31:28

William Barney

I am embarrassed but I have been trying to write macros and I am terrible at it. I have Excel Tips "The Macros Sixth edition" following the instructions but I am missing something because nothing seems to work. I get thru the developer tab and start writing the macros, but there is where I seem to run out of steam. The macros seem to be complete ( I copied one of yours) yet nothing happens. Can you give a dumbass a few tips. R Bill


2015-03-07 16:29:22

JG ENTERPRISES

To: Jerry 07 Mar 2015, 10:52
"Another way to open and resize multiple workbooks on the screen is to create an Excel Workspace"
As far as I know, "Workspace" is not available in version 2013. I used that feature all the time in previous versions and was very disappointed when I found that MS had removed it. If someone knows differently please let me know.


2015-03-07 10:52:51

Jerry

Another way to open and resize multiple workbooks on the screen is to create an Excel Workspace. To do this, open the workbooks and manually resize them. You can also scroll to the columns/rows you want to have on the screen. Then on the View tab, click Save Workspace. Give it a name in the Save Workspace dialog box (for some reason, it always defaults to Resume.xlw!), select the folder where you want to save it, and click Save. The resulting .xlw file contains information about the files that are to be opened, their size and location on the screen, and the starting column/row to be displayed. The workspace file does NOT contain the actual data; that is still stored in the individual files. After the workspace has been created, simply go to File, Open and select the workspace file. It will automatically open and position the files the way they appeared the last time you saved the workspace.
If you often work with multiple files, a handy command button to add to the QAT is "Close All". You can find it in the All Commands section in Customize the Quick Access Toolbar. This will close each of the open files in that instance of Excel and prompt you to save the changes to each file. Saving changes to the files does NOT save the view opened by the Workspace, nor does saving the workspace save the changes to the files.


2015-03-07 09:17:28

Khushnood Viccaji

I tried this macro and it works just fine. Very interesting!

Just one small problem:
the Windows.Count = 2 check also includes hidden workbooks (in my case, PERSONAL.XLSB).

As a workaround, I inserted a few lines in the macro, to check only the visible open workbooks, as under:

...
Dim Top2 As Single
Dim iVisibleWbkCount As Integer, wbk As Window ' (new variables)

'set the count of visible workbooks to 0
iVisibleWbkCount = 0
'cycle thru each workbook and increment the visible workbooks count by 1
'if it is visible
For Each wbk In Application.Windows
If wbk.Visible = True Then iVisibleWbkCount = iVisibleWbkCount + 1
Next wbk

If iVisibleWbkCount = 2 Then
With Windows(1)
...


I would love to hear of a better / more efficient workaround :)


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.