Show or hide rows in each sheet except Master sheets

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

LastRow = SheetName.UsedRange.Rows.Count

Invalid qualifer

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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”
42213-Farrukh

Code:

Sub master()
    test ("m1")
    test ("m2")
End Sub

Sub test(sheetName As String)
    Sheets(sheetName).Activate
    Range("a30").Value = 100
End Sub

Geof

Reply | Quote

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

Reply | Quote

Invalid qualifer

Yes, that should have been Sheets(SheetName) instead of just SheetName, for all instances obviously.

cheers, Paul

Reply | Quote

Hi Respected,
Thank you for your suggested code works good & faster.

Reply | Quote