VBA Resize columns (not rows) to Fit Screen

gmooney

Active Member
Joined
Oct 21, 2004
Messages
252
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that contains many many rows and quite a few columns....Their are far to many rows to try to get them to fit on one screen but the columns can be.

I created this spreadsheet on an external monitor but now they do not fit on my laptop.

I have seen ways to do autofit but that does both columns and rows. I only want the columns (columns A-V) to fit and will let the user scroll down rows. Columns beyond column V can be on the next screen over.

Any help appreciated.
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Paste this in a module and activate by a command button on the sheet in question:

Code:
Option Explicit
Sub fitall()


    Range("A:V").Select
    ActiveWindow.Zoom = True
    Cells(1, 1).Select


End Sub


If you want all sheets to automatically resize A:V, paste this macro in the ThisWorkbook module :


Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Range("A:V").Select
    ActiveWindow.Zoom = True
    Cells(1, 1).Select
End Sub
 
Upvote 0
Thanks Logit.....I want this to automatically happen when the file is opened and would like to not have to activate it via a command button.

Another thing....the file always opens to another sheet and that sheet is not the sheet that I need autoadjusted...
 
Upvote 0
Use this macro :

Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Range("A:V").Select
    ActiveWindow.Zoom = True
    Cells(1, 1).Select
End Sub

There is nothing in this macro that makes the workbook open to a specific sheet. Must be something else going on in your workbook.
 
Last edited:
Upvote 0
Hi Logit...you are correct...I have a macro that changes the sheet to a certain sheet every-time the file is opened as a starting point. Based on some of your insights I have now figured out that I would like every worksheet and chart sheet to be able to be adjusted to screen fit......I have 2 chart charts that I would like to be able to fit to selection and then 2 worksheets to fit to column width for A-V in one sheet and A-R in the other.

Can you help with that?
 
Upvote 0
.
For the sheet to auto-open to Col A:V ... paste this macro in the Sheet Module :


Code:
Option Explicit


Private Sub Worksheet_Activate()


    Range("A:V").Select
    ActiveWindow.Zoom = True
    Cells(1, 1).Select
End Sub


For the sheet to auto-open to Col A:R ... paste this macro in the Sheet Module :


Code:
Option Explicit


Private Sub Worksheet_Activate()


    Range("A:R").Select
    ActiveWindow.Zoom = True
    Cells(1, 1).Select
End Sub


I tested both macros here with a chart on each sheet. The charts auto-resized. Let me know if the charts don't auto-resize for you.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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