This VBA Code takes 3 minutes to execute. Need alternative.

[Ioweyouone]

Hello,

The objective is to hide rows based on the value in a cell. I wrote a formula for all cells in the range M141:M1500, that returns a 1 if I want that row hidden. =COUNTA(D141:I141)+IF(M140>2,1,0)
The attached image shows the Code I wrote to hide all the rows with a 1 value in column M. It works, but it takes over 3 minutes to hide the rows after I execute the command. Any suggestions to speed this up?

 

[6StringJazzer]

First, I recommend that when you want to share code you paste the code directly into your post, instead of taking a picture of the code. After you paste, select the code and click the VBA button to apply code tags.

Before the loop add

Application.ScreenUpdating = False

and after the loop add

Application.ScreenUpdating = True

That will help but if it's not enough let us know. There might be a couple of other tricks, like building the rows into one range and hiding them all at once.

 

[6StringJazzer]

This works but I haven't done any performance testing.

Private Sub HideThem()

   Dim Cell As Range
   Dim WholeRange As Range
      
   Application.ScreenUpdating = False
   
   For Each Cell In Range("A1:A32")
      If Cell.Value = 1 Then
         If WholeRange Is Nothing Then
            Set WholeRange = Cell
         Else
            Set WholeRange = Union(WholeRange, Cell)
         End If
      End If
   Next Cell
   WholeRange.EntireRow.Hidden = True

   Application.ScreenUpdating = True

End Sub

 

[Armstrong_N14]

maybe just a filter code would do the charm?

Range("$M$141:$M$1499").Select
Selection.AutoFilter
ActiveSheet.Range("$M$141:$M$1499").AutoFilter Field:=1, Criteria1:="=1", _
Operator:=xlAnd

 

[MARK858]

Shouldn't that be "<>1" if you want that row hidden using AutoFilter (the range should also start in M140 as autofilter requires a header)

Sub Test()
ActiveSheet.Range("M140:M1499").AutoFilter 1, "<>1"
End Sub

 

[Ioweyouone]

Works great. Thank you all for your help.

 

[MARK858]

You're welcome, happy we could help