Selecting and hiding multiple rows simultaneously (NO LOOPING)

Steve_CDHB

New Member
Joined
Jun 25, 2012
Messages
3
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Jun 25, 2012
Messages
3
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
728 line .....0.107 secs

Code:
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Hi Steve,

See if this improves the speed:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top