Page Orientation Macro

agc3278

New Member
Joined
Nov 14, 2007
Messages
15
I have a macro that hides and un-hides rows, which was graciously created with the help of this forum (see below). I am looking to adjust it (if possible) so that depending on the number of rows visible, the page orientation will change from portrait to landscape, and vice versa.

If it is not possible, I would love some assistance on writing a separate macro to do what I have described. Any help would be greatly appreciated.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim aNames, Val
  Dim i As Long, ShowHideRows As Long, ShowRows As Long
  
  Const sNames As String = "Sheet 1"
  Const RowCell As String = "D1"
  Const FirstVisRow As Long = 4
  Const MaxHideRow As Long = 41
  
  If Target.Address(0, 0) = RowCell Then
    aNames = Split(sNames, ", ")
    ShowHideRows = MaxHideRow - FirstVisRow + 1
    Val = Target.Value
    ShowRows = IIf(IsEmpty(Val), ShowHideRows, Val)
    Application.ScreenUpdating = False
    For i = 0 To UBound(aNames)
      With Sheets(aNames(i)).Rows(FirstVisRow)
        .Resize(ShowHideRows).Hidden = True
        If ShowRows > 0 Then .Resize(ShowRows).Hidden = False
      End With
    Next i
    Application.ScreenUpdating = True
  End If
End Sub
 
Last edited:

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
How about adding something like this to your code? Add it after Next i...granted you will need to determine what the value of rowamount is going to be
Code:
Application.PrintCommunication = False
    With ActiveSheet.PageSetup
if rowamount => then
        .Orientation = xlLandscape
else
.Orientation = xlportrait
endif
    End With
Application.PrintCommunication = True
 

agc3278

New Member
Joined
Nov 14, 2007
Messages
15
Thanks so much! I will give it a try when I'm back at work. Will the code start on the next line where the Application.Screenupdating part would be, and then bookend it with the screen updating part?
 

Forum statistics

Threads
1,085,333
Messages
5,383,021
Members
401,812
Latest member
topherj09

Some videos you may like

This Week's Hot Topics

Top