Looping hide row Optimization problem

**escobf** · 04-09-2013, 03:06 PM

I am working with a fairly large dataset for which I am trying to develop the quickest code possible that will:
1. Find the rows (from 10 to 16009) in the fourth column whose value =0
2. Hide the entire row where this condition is true

Here is the code I am working with, however it obviously very slow. I would appreciate any advice as to how to improve this or utilize a better method.

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False

    Dim i As Integer
    Dim r As Integer
    Dim x As Variant
    Dim y As Variant
    x = Array(10)
    y = Array(16009)

    For i = 0 To 2
        For r = x(i) To y(i)
            If Cells(r, 4).Value = "0" Then
                Rows(r).EntireRow.Hidden = True
            Else
                Rows(r).EntireRow.Hidden = False
            End If
        Next r
    Next i

    Application.ScreenUpdating = True
    Application.Calculate
End Sub

**Jakobshavn** · 04-09-2013, 04:06 PM

This will be much faster:

Sub RowHider()
Dim rHide As Range: Set rHide = Nothing
Cells.EntireRow.Hidden = False
For i = 10 To 16009
    Set r = Cells(i, "D")
    If r.Value = 0 Then
        If rHide Is Nothing Then
            Set rHide = r
        Else
            Set rHide = Union(rHide, r)
        End If
    End If
Next i

If rHide Is Nothing Then
Else
    rHide.EntireRow.Hidden = True
End If
End Sub

The speed comes from only "hiding" once!

**escobf** · 04-09-2013, 04:26 PM

Thank you, it works much better! I see what you did by only hiding once. Since I want this to update the sheet each time the user navigates to it, I put the code back under the Worksheet_Activate() Private sub- which should not affect speed from what I can observe.

How might I add a popup msgbox that would tell the user to wait while the page is update? Is it possible to include some sort of "time left" notification in this msgbox?

Thanks again.

**Jakobshavn** · 04-09-2013, 04:50 PM

I would display a TextBox before the loop runs and remove it after the loop runs.

**escobf** · 04-09-2013, 05:15 PM

Great thanks, I am experimenting with different ways of doing this but it is not a major issue.
However relating to this row hide loop- I will need to add a filter to the dataset but it seems that the rows do not remain hidden whenever the data is filtered according to a column parameter and then reset back without the filter. Does applying/removing a filter always undo hidden rows? Is there any way to get around this?

**mehmetcik** · 04-09-2013, 04:51 PM

Is it possible that this approach might be faster?

Sub Select_Select()


    Dim rngFind As Range
    Dim strValueToPick As String
    Dim rngPicked As Range
    Dim rngLook As Range
    Dim strFirstAddress As String

    Application.ScreenUpdating = False

     Columns("D:D").Select
    
    Set rngLook = Selection
    strValueToPick = "0"
    With rngLook
    
        Set rngFind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Set rngPicked = rngFind
            Do
                Set rngPicked = Union(rngPicked, rngFind)
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
        End If
    End With
    
    If Not rngPicked Is Nothing Then
        rngPicked.EntireRow.Hidden = True
    End If

End Sub

**escobf** · 04-09-2013, 05:20 PM

Thank you for your solution Mehmetcik, however although it does run much quicker the code is not hiding the correct rows (those in column 4 which have a 0, between rows 10 and 16009). I think the issue with your solution lies in that it is selecting row 1 column 1 as the starting point- I am also hesitant about using Select as well as xlWhole which might seem to take longer to me, however please explain how this would be quicker.

**mehmetcik** · 04-10-2013, 02:05 PM

Hi

I amended my code to only look at column D.

The find function is in true vba code and will run faster then anything that has a loop.

Try it and see which is faster.

Do you have a timer programme?

**mehmetcik** · 04-09-2013, 05:19 PM

I forgot to select Column D.

Sorry.

**tigeravatar** · 04-09-2013, 06:22 PM

escobf,

Another way (assumes data starts in column A and row 9 is a header row):

Private Sub Worksheet_Activate()
    
    Dim lCalc As XlCalculation
    
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    Intersect(Me.UsedRange, Me.Range("9:16009")).AutoFilter 4, "<>0", xlFilterValues
    
    With Application
        .Calculation = lCalc
        .ScreenUpdating = True
    End With
    
End Sub

Looping hide row Optimization problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Re: Looping hide row Optimization problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions