VBA to auto-resize worksheet to fill the screen

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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
 
Upvote 0
@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.
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Change

.Range("A1").Select

to This

Application.Goto .Range("A1"), True
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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
Back
Top