View Full Version : [SOLVED:] Limiting Window Size to the used Column range
nikki333
05-17-2018, 12:50 PM
Hi Folks
Is there a way to adjust the Excel window size to the sheet contents...ie. for example, if a sheet contains a table spanning from A:G, i'd like the window width to adjust, so that the scroll bar is just right of the column G, however still at 100% magnification. And then the horizontal scroll bar should disappear, since then it wouldn't be useful anyways.
Cheers
mancubus
05-18-2018, 04:50 AM
select the range (Ctrl + A for used range), then
...ribbon
........view
............zoom
................zoom to selection
or, with VBA
ActiveSheet.UsedRange.Select
ActiveWindow.Zoom = True
nikki333
05-18-2018, 06:33 AM
Thank you for your quick response. However, it's actually the opposite that I wanted to do...ie. to fit the application window to the used column width.
I have tried to set the application width to the width of a shape on my sheet like so:
worksheet.width = myshape.width
But this does not take into account the width of the vertical scroll bar, so the window is slightly to narrow
worksheet.width = myshape.width
But this does not take into account the width of the vertical scroll bar, so the window is slightly to narrow
worksheet.width = myshape.width + 10 'or 20 or ???
Paul_Hossler
05-18-2018, 01:50 PM
This resizes the application window to the (1,1) current region width and hides the horiz scrollbar
22274
Option Explicit
'https://msdn.microsoft.com/en-us/library/windows/desktop/ms724385(v=vs.85).aspx
Declare Function GetSystemMetrics32 Lib "user32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
Public Const SM_CXVSCROLL = 2
Public Const SM_CXBORDER = 5
Sub Fit()
Dim r As Range
Set r = ActiveSheet.Cells(1, 1).CurrentRegion
Application.WindowState = xlNormal
Application.Width = r.Width + 2 * GetSystemMetrics32(SM_CXVSCROLL) + 4 * GetSystemMetrics32(SM_CXBORDER)
ActiveWindow.DisplayHorizontalScrollBar = False
End Sub
nikki333
05-19-2018, 11:46 AM
Thanks Paul, that's exactly what I was looking for
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.