VBA Resize columns (not rows) to Fit Screen

[gmooney]

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.

 

[Logit]

Paste this in a module and activate by a command button on the sheet in question:

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 :

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

 

[gmooney]

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...

 

[Logit]

Use this macro :

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.

 

[gmooney]

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?

 

[Logit]

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

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 :

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.