Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide rows on visible sheets (2003 SP2)

    I am trying to automate hiding rows across all visible sheets via VBA. I want the code to test which rows to hide in the active sheet, then hide the same row across all visible sheets.

    I can do it across all sheets OK, however it is necessary that certain sheets remain hidden and unaffected.

    The code I am using hides hidden rows fine, and selects all visible sheets OK, but only hides the required rows on the active sheet.

    My methodology below is to determine which row to hide, then select all visible sheets and hide that row.


    My code is:

    Sub Main()
    Dim Counter As Integer
    Application.ScreenUpdating = False
    Dim mySheet As Object

    ' Initialize variables.
    Counter = 1

    'set up the range
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = lngMaxRow To 7 Step -1

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hide rows on visible sheets (2003 SP2)

    Hi,
    You could try this:
    <pre>Sub Main()
    Dim Counter As Long, lngIndex As Long, lngMaxRow As Long, lngRow As Long
    Dim alngRows() As Long
    Application.ScreenUpdating = False
    Dim mySheet As Worksheet
    'set up the range
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = lngMaxRow To 7 Step -1 'Only go up to row 7

    ' Test cells in column A and X
    If Cells(lngRow, 17) = 0 And Cells(lngRow, 18) = "" Then
    ReDim Preserve alngRows(Counter)
    alngRows(Counter) = lngRow
    Counter = Counter + 1
    End If
    Next lngRow
    'Select all visible sheets
    For Each mySheet In Worksheets
    With mySheet
    If .Visible = True Then
    For lngIndex = LBound(alngRows) To UBound(alngRows)
    .Rows(alngRows(lngIndex)).Hidden = True
    Next lngIndex
    End If
    End With
    Next mySheet
    Application.ScreenUpdating = True
    End Sub
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hide rows on visible sheets (2003 SP2)

    Or:

    Sub Main()
    Dim wsh As Worksheet
    Dim lngRow As Long
    Dim lngMaxRow As Long

    Application.ScreenUpdating = False

    ' Set up the range
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = lngMaxRow To 7 Step -1 'Only go up to row 7
    ' Test cells in column Q and R
    If Cells(lngRow, 17) = 0 And Cells(lngRow, 18) = "" Then
    For Each wsh In Worksheets
    If wsh.Visible = xlSheetVisible Then
    wsh.Rows(lngRow).Hidden = True
    End If
    Next wsh
    End If
    Next lngRow

    Application.ScreenUpdating = True
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide rows on visible sheets (2003 SP2)

    Hans, I get the following message:
    'unable to set the hidden property of the range class'
    when the code hits:
    wsh.Rows(lngRow).Hidden = True
    any thoughts?
    cheers!

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide rows on visible sheets (2003 SP2)

    Hi Hans,
    I figured it out - one of the sheets was protected.
    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •