Page Orientation Macro

[agc3278]

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.

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

 

[Roderick_E]

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

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
if rowamount => then
        .Orientation = xlLandscape
else
.Orientation = xlportrait
endif
    End With
Application.PrintCommunication = True

 

[agc3278]

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?