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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Need to understand who is using a particular workbook? There are a number of ways you can find out, as discussed in this tip.
Discover MoreMany Windows applications rely on information stored in the Registry. If that information cannot be accessed, the ...
Discover MoreNeed to modify how a cell reference, in a formula, is constructed? The shortcut described in this tip will help you step ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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 :)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Comments