Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Show or hide rows in each sheet except Master sheets

    Hi All,
    The below code currently worked on active sheet fine, i want to change to run on two sheets Master1, Master2 rather then all?

    Code:
    Option Explicit
    Sub RowsVisibleUnVisible()
    Dim i As Long
    Dim LastRow As Long
    'turn off screenupdating so the screen does not flick
    Application.ScreenUpdating = False
    'count whats the lst cell in the sheet using Column "A" as refference
    ''LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastRow = Sheet2.UsedRange.Rows.Count
    'go through all rows from number 1 to the last cell determined above
    
    For i = 1 To LastRow
        'if the value of the cell in column "D" is null, or empty then
        If Cells(i, 4).Value = vbNullString Then
            'if the above is true then hide the entire row
            Cells(i, 4).EntireRow.Hidden = True
        Else
            'for any other scenario including that there is a value then unhide the row
            Cells(i, 4).EntireRow.Hidden = False
        End If
    Next i
        
    Application.ScreenUpdating = True
    End Sub
    Last edited by farrukh; 2015-10-04 at 02:49. Reason: i want to change to run on two sheets Master1, Master2 rather then all?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You have already used a sheet name to collect the number of rows, which will cause problems if the active sheet isn't "Sheet2".
    You can use the sheet name for the whole lot.

    Note: not tested

    cheers, Paul

    Code:
    Sub UpdateMasterSheets()
    	RowsVisibleUnVisible("Master1")
    	RowsVisibleUnVisible("Master2")
    End Sub
    
    Sub RowsVisibleUnVisible(SheetName As String)
    Dim i As Long
    Dim LastRow As Long
    'turn off screenupdating so the screen does not flick
    Application.ScreenUpdating = False
    'count whats the lst cell in the sheet using Column "A" as refference
    ''LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastRow = SheetName.UsedRange.Rows.Count
    'go through all rows from number 1 to the last cell determined above
    
    For i = 1 To LastRow
        'if the value of the cell in column "D" is null, or empty then
        If SheetName.Cells(i, 4).Value = vbNullString Then
            'if the above is true then hide the entire row
            SheetName.Cells(i, 4).EntireRow.Hidden = True
        Else
            'for any other scenario including that there is a value then unhide the row
            SheetName.Cells(i, 4).EntireRow.Hidden = False
        End If
    Next i
        
    Application.ScreenUpdating = True
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    LastRow = SheetName.UsedRange.Rows.Count
    Invalid qualifer

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi
    Do you need activesheet.UsedRange ..

    If so then you might have to activate the sheet first
    Code:
    Application.ScreenUpdating = False
    Sheets(sheetName).activate
    LastRow = activesheet.Usedrange .Rows.Count
    Geof
    Last edited by geofrichardson; 2015-10-04 at 12:15. Reason: remove double quotes

  5. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by geofrichardson View Post
    Hi
    Do you need activesheet.UsedRange ..

    If so then you might have to activate the sheet first
    Code:
    Application.ScreenUpdating = False
    Sheets("sheetName").activate
    LastRow = activesheet.Usedrange .Rows.Count
    Geof

    I dont want to activate the sheets only needs to run the code on specific two sheets.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi again

    The code below will only operate on the two sheets, m1 and m2 shown in the screenshot.
    Each of the sheets m1 and m2 are activated in turn and the value 100 is inserted into "A30"
    Farrukh.png

    Code:
    Sub master()
        test ("m1")
        test ("m2")
    End Sub
    
    Sub test(sheetName As String)
        Sheets(sheetName).Activate
        Range("a30").Value = 100
    End Sub
    Geof

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi farrukh

    Whenever you perform a Print Preview, Excel sets DisplayPageBreaks to True.
    When this setting is true, for each and every individual row that gets hidden or unhidden, the application has to "redraw" the page breaks.
    This can be a very slow process. So it is a good idea to turn OFF any PageBreak view mode first.

    Also, for fastest operation, instead of hiding each relevant row one-at-a-time, we can 'gather' the rows we want to hide in a 'collection', and then hide them all in one operation.
    Here's the code I would suggest:
    Code:
    Sub test1()
    Dim zSht As String
    
    zSht = ActiveSheet.Name                             'save current setting, for later restore
    
    hideRows "Master1"                                  'run routine for specified sheet
    hideRows "Master2"                                  'run routine for specified sheet
    
    Worksheets(zSht).Activate                           'return to original sheet
    
    End Sub
    Code:
    Sub hideRows(zSheet)
    
    Dim zStartRow As Long
    Dim zEndRow As Long
    Dim zRow As Long
    Dim zCalcMode As Long
    Dim zViewMode As Long
    Dim zRng As Range
    
    zCalcMode = Application.Calculation                 'save current setting, for later restore
    
    Application.Calculation = xlCalculationManual       'turn calcs OFF for speed
    Application.ScreenUpdating = False                  'turn screen refresh OFF for speed
    
    Worksheets(zSheet).Activate                         'switch to required sheet
    Cells.EntireRow.Hidden = False                      'unhide ALL rows on sheet first
    
    With ActiveSheet                                    'use shortcut
    .Select                                             'select sheet to allow change to view mode
    zViewMode = ActiveWindow.View                       'save current setting, for later restore
    ActiveWindow.View = xlNormalView                    'turn OFF any Page Break or Page layout view
    .DisplayPageBreaks = False                          'turn OFF for speed
    
    'Set the start and end row for processing loop..
    zStartRow = .UsedRange.Cells(1).Row
    zEndRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
    For zRow = zStartRow To zEndRow                     'loop through data rows
    With .Cells(zRow, "D")                              'check column [D] entries
        If .Value = vbNullString Then
            If zRng Is Nothing Then                     'this is the first entry, so define range..
            Set zRng = .Cells                           'initial range for rows to hide
            Else                                        'otherwise..
            Set zRng = Application.Union(zRng, .Cells)  'add the matching cell to the range collection
            End If
        End If
    End With
    Next zRow                                           'process next data row
    
    End With
    
    If Not zRng Is Nothing Then zRng.EntireRow.Hidden = True
    
    'RESET
    ActiveWindow.View = zViewMode
    Application.Calculation = zCalcMode
    
    End Sub
    See attached example file.

    zeddy
    Attached Files Attached Files

  8. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2015-10-05)

  9. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi Respected,
    Thank you for your suggested code works good & faster.

  10. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Quote Originally Posted by farrukh View Post
    Invalid qualifer
    Yes, that should have been Sheets(SheetName) instead of just SheetName, for all instances obviously.

    cheers, Paul

  11. The Following User Says Thank You to Paul T For This Useful Post:

    farrukh (2015-10-09)

Posting Permissions

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