VBA to auto-resize worksheet to fill the screen

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, this is my second attempt to solve this, the last one ended badly. By badly, I mean with me just a confused simpleton. I'm thinking that what I am trying to do can't be all that difficult but I can't get it to work. Could really use some help here.

I have a workbook with 12 worksheets, all of them are of varying widths as far as the number and size of the columns. My goal is to have the workbook open on anyone's computer and 'fill the screen' horizontally, all the columns with data from left to right, no white space, especially to the right side. I don't care about the vertical aspect because the users can easily scroll up and down. I just don't want them to have to scroll left or right. I found this little piece of code but when I email the workbook to a different computer, the columns with data are all 'selected' but there are still five empty columns to the right.
Code:
Private Sub Workbook_Open()
With Sheets("Sheet18")
Columns("A:L").Select
ActiveWindow.Zoom = True
Range("A1").Select
End With
End Sub
Also, I have 12 worksheets that are using anywhere from 5 to 20 columns so I'm not sure how to incorporate that fact into the code either. I was just trying to get it to work with this one worksheet to start. Any help is much appreciated. Thanks!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Change to the following and give it ago. I've just tested on a new workbook hence sheet1

VBA Code:
Private Sub Workbook_Open()
With Sheets("Sheet1")
.Columns("A:L").Select
End With
 ActiveWindow.Zoom = True
 Range("A1").Select
End Sub
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Trevor G ok, that works! Now, how do I get the same thing to work on the other 11 sheets? :)
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
In the workbook event you could use the SheetActivate but this will do if for every sheet in your workbook.

VBA Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Columns("A:L").Select
ActiveWindow.Zoom = True
Range("A1").Select
End Sub
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Trevor G Is there another way? As I mentioned in my initial post, the worksheets have anywhere from 5 to 20 columns so the "A:L" would present a problem.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Trevor G Actually, I may have spoken too soon in post #3. When I last saved the workbook, Sheet11 was the sheet visible on my laptop. I then emailed the workbook to my desktop and when I opened it, Sheet11 was full width and Sheet18 was displaying 7 columns of white space to the right of my data! Weird! I checked the VBA code and it still shows Sheet 18 in it.
Code:
Private Sub Workbook_Open()
With Sheets("Sheet18")
.Columns("A:L").Select
End With
 ActiveWindow.Zoom = True
 Range("A1").Select
End Sub
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I've recorded a macro to select the cells from Column A to the right so if there is more than column L it will include them. If you add the code behind each sheet you want to zoom to then it will work for you. Start by right clicking one of the sheets and select View Code then change the two drop down to do use the following, then whilst in the VBA screen just double click each sheet name and add the code so the code will be behind each sheet.

VBA Code:
Private Sub Worksheet_Activate()
Range(Selection, Selection.End(xlToRight)).Select
    ActiveWindow.Zoom = True
    Range("A1").Select
End Sub
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,165
Office Version
  1. 2016
Platform
  1. Windows
See if this works for you

Place code in the ThisWorkbook Module:
VBA Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim lRightMostColumnIndex  As Long
 
    Application.ScreenUpdating = False
 
    With Sh
        lRightMostColumnIndex = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
        .Range(.Columns(1), .Columns(lRightMostColumnIndex)).Select
        ActiveWindow.Zoom = True
        .Range("A1").Select
    End With

End Sub

EDIT:
Make sure that there are no formatted cells beyond the rightmost column containing data .. If there are any formatted cells there, you will need to clear their formatting, otherwise the UsedRange Object will include those cells as well and you will end up with erroneous results.
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,165
Office Version
  1. 2016
Platform
  1. Windows
Change

.Range("A1").Select

to This

Application.Goto .Range("A1"), True
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Jaafar Tribak I am getting a 'compile error, expected function or variable'. The 'private sub workbook' line is highlighted in yellow and the '.GoTo' is highlighted in blue.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,087
Messages
5,622,628
Members
415,915
Latest member
Eng Said Ebead

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top