Selecting and hiding multiple rows simultaneously (NO LOOPING)

[Steve_CDHB]

Hi everyone,

I currently have the looping process below that checks for values in column E = 0 and if true hides the row.

Have done some error checking and the time hungry piece of code is "a.EntireRow.Hidden = (a.Value = 0)"

Is there a simple way to build a selection which has multiple rows and then hide the entire selection at once to speed this process up?

Sub HideBlankRows()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim a As Range, s As String
s = "e3:e728" 'The range to check on each sheet
ThisWorkbook.Worksheets("Staffing Tool").Select

For Each a In Range(s)
a.EntireRow.Hidden = (a.Value = 0)
Next a

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


Thanks in advance to anyone that can assist with my problem.

 

[tusharm]

With just 700 rows, I am surprised you have a performance problem. Do you have any event macros that are getting triggered? If so, consider *temporarily* disabling events. For more look up Excel VBA help for EnableEvents.

If you still want another option, it would be to use the range.find method. You can "black box" the work by using the code at
FindAll
http://www.tushar-mehta.com/excel/tips/findall.html

Hi everyone,

I currently have the looping process below that checks for values in column E = 0 and if true hides the row.

Have done some error checking and the time hungry piece of code is "a.EntireRow.Hidden = (a.Value = 0)"

Is there a simple way to build a selection which has multiple rows and then hide the entire selection at once to speed this process up?

Sub HideBlankRows()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim a As Range, s As String
s = "e3:e728" 'The range to check on each sheet
ThisWorkbook.Worksheets("Staffing Tool").Select

For Each a In Range(s)
a.EntireRow.Hidden = (a.Value = 0)
Next a

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


Thanks in advance to anyone that can assist with my problem.

 

[Michael M]

I noticed your macro is called HideBlankRows.
Are the cells in column "E" blank or contain 0

If they are blank, an Autofilter would be far quicker.
But as Tushar said, 700 rows should be quick no matter which way you do it.

 

[Steve_CDHB]

Hi Guys,

I have timed the HideBlankRows macro which is triggered by a worksheet_change event. to loop through all 725 rows this takes 3mins 10secs which i personally think is terrible. It may be due to the pc Im running this on but Im using a Toshiba laptop with Intel Core i5 CPU 2.4Ghz and 3GB of RAM.

The issue is that eventually this workbook will be given out to staff within my organisation to see how well they have rostered staff (in this case nurses) to match expected demand (patients) on a given day. The 3min thing is a real pain and will mean no-one picks this up and uses it.

Will try the Findall and let you know how it goes.

Thanks for the suggestions.

 

[tusharm]

Like I wrote in my previous post, disable events before you start processing the code though it is critical to enable events at the end.

In addition to the help file, see
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL events.htm

specifically the section
An Excel specific trap: the EnableEvents property

Hi Guys,

I have timed the HideBlankRows macro which is triggered by a worksheet_change event. to loop through all 725 rows this takes 3mins 10secs which i personally think is terrible. It may be due to the pc Im running this on but Im using a Toshiba laptop with Intel Core i5 CPU 2.4Ghz and 3GB of RAM.

The issue is that eventually this workbook will be given out to staff within my organisation to see how well they have rostered staff (in this case nurses) to match expected demand (patients) on a given day. The 3min thing is a real pain and will mean no-one picks this up and uses it.

Will try the Findall and let you know how it goes.

Thanks for the suggestions.

 

[Michael M]

728 line .....0.107 secs

Sub HideBlankRows()
Application.ScreenUpdating = False
Dim r As Long
    For r = 728 To 3 Step -1
        If Range("E" & r).Value = 0 Then Rows(r).EntireRow.Hidden = True
    Next r
Application.ScreenUpdating = True
End Sub

 

[Trebor76]

Hi Steve,

See if this improves the speed:

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?642888-Selecting-and-hiding-multiple-rows-simultaneously-(NO-LOOPING)
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
  
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    With Sheets("Staffing Tool")
        .Range("E3:E728").Rows.Hidden = False
        On Error Resume Next 'Account for no data on the 'Staffing Tool' tab.
            lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        On Error GoTo 0
        If lngMyCol <= 5 Then
            lngMyCol = 6
        End If
        With .Columns(lngMyCol)
            With Range(Cells(3, lngMyCol), Cells(728, lngMyCol))
                .Formula = "=IF(E3=0,""HIDE"","""")"
                .Calculate
                .Value = .Value
            End With
            .Replace "HIDE", "#N/A", xlWhole
            On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
                .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Hidden = True
            On Error GoTo 0 'Turn error reporting back on
            .Delete
        End With
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

HTH

Robert