PDA

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

SamT
05-18-2018, 11:20 AM
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