Snasui Excel Forum

Code: Select all

Public Sub Hidden_toggle()
    Dim i As Integer, j As Integer
    Dim lLastRow As Long
    Dim rPntAR As Range
    
    Application.ScreenUpdating = False
    With ActiveSheet
        .Unprotect
        For i = 11 To 26 Step 3
            If .Cells(4, i).Value > 0 Then j = i + 1
        Next i
        lLastRow = .Range("a" & Rows.Count) _
                                .End(xlUp).Offset(1, 0).Row
        
        If .Columns(26).Hidden = True Then
            .Range(.Columns(12), .Columns(26)).Hidden = False
            .PageSetup.PrintArea = .Range("a1").Address(0, 0) & ":" & _
                                    .Range("z" & lLastRow).Address(0, 0)
        Else
            .Range(.Columns(j), .Columns(26)).Hidden = True
            .PageSetup.PrintArea = .Cells(1, 1).Address(False, False) & ":" & _
                                    .Cells(lLastRow, j - 1).Address(False, False)
        End If
        
        .Protect
        .EnableSelection = xlUnlockedCells
    End With
    
End Sub

Code: Select all

...
If .Columns(26).Hidden = True Then
    .Range(.Columns(12), .Columns(26)).Hidden = False
    .PageSetup.PrintArea = .Range("a1").Address(0, 0) & ":" & _
        .Range("z" & lLastRow).Address(0, 0)
    .PageSetup.FitToPagesWide = 1
Else
    .Range(.Columns(j), .Columns(26)).Hidden = True
    .PageSetup.PrintArea = .Cells(1, 1).Address(False, False) & ":" & _
        .Cells(lLastRow, j - 1).Address(False, False)
    .PageSetup.FitToPagesWide = 1
End If
...