Macro FOR loop slowing down excel

[Tiobyte]

Hi all,

I am new to forum and VBA and having an issue with a macro i wrote and hoping someone can help me.

I have a basic for loop that works however as soon as it's finished excel slows right down and i cant click anything on the ribbon after.

Sub glenmor()'
' glenmor Macro
'


'
    Application.ScreenUpdating = False
    Dim lastRow As Long, lastRow2 As Long, r As Long
    
    Sheets("Data").Select
    lastRow = 4000
    lastRow2 = 1
    
    On Error Resume Next
        For r = lastRow To 2 Step -1
            If Range("B" & r).Value = "Glenmor" And Range("E" & r).Value = 0 Then
                Rows(r).Copy Destination:=Worksheets("Glenmor").Range("A" & lastRow2 + 1)
                lastRow2 = lastRow2 + 1
                Else:
            End If
        Next r
    
   
    Sheets("Results").Select
    Application.ScreenUpdating = True


End Sub

Any suggestions welcome
Thanks

 

[Fluff]

Hi & welcome to the board.
How about

Sub glenmor()
   Application.ScreenUpdating = False
   Dim lastRow As Long, lastRow2 As Long, r As Long
   
   With Sheets("Pcode")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:E1").autofilter 2, "Glenmore"
      .Range("A1:E1").autofilter 5, "=0"
      .autofilter.Range.Offset(1).Copy Sheets("Glenmore").Range("A2")
      .AutoFilterMode = False
   End With
   Application.ScreenUpdating = True
End Sub

 

[Tiobyte]

Hi & welcome to the board.
How about

Sub glenmor()
   Application.ScreenUpdating = False
   Dim lastRow As Long, lastRow2 As Long, r As Long
   
   With Sheets("Pcode")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:E1").autofilter 2, "Glenmore"
      .Range("A1:E1").autofilter 5, "=0"
      .autofilter.Range.Offset(1).Copy Sheets("Glenmore").Range("A2")
      .AutoFilterMode = False
   End With
   Application.ScreenUpdating = True
End Sub

Brilliant, thanks so much!

I gave this ago and it sorts my data so much quicker! I do have a quick question about the code you posted.
How does it know when to stop searching, for example does it stop when it comes across blanks for the selected range?

Once again thanks!

 

[Fluff]

Glad to help & thanks for the feedback

How does it know when to stop searching, for example does it stop when it comes across blanks for the selected range?

Yes, it stops when there is an entire blank row in the specified range (in this case A:E)

 

[Tiobyte]

Thanks,

One last question then I am done.

Can i apply 2 filters or operators to the same column. For example i want it to only show the data for >0 AND <100. Second example would be i want to show =0 OR =100
Hope that makes sense.

 

[Fluff]

Yes you can both of those, have a look here for examples

http://www.bluepecantraining.com/portfolio/excel-vba-how-to-filter-data-using-autofilter/