0

I have this little code that what basically do is loop trough a table, go name by name, filter the data by that specific name and count how many times the person hit a goal. But, there is a small amount of names (104) and it takes a lot of time. I would like to know if there is a way to speed up that process.

Here is the code:

lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

 
For i = 2 To Worksheets("Sheet1").UsedRange.Columns.Count Step 2
On Error Resume Next
 Worksheets("Data").ShowAllData
 
 Worksheets("Sheet1").Activate
    For x = 4 To lastrow
    Worksheets("Data").ShowAllData
       name = Worksheets("Sheet1").Cells(x, 1)
       month = Worksheets("Sheet1").Cells(3, i)
        Worksheets("Data").Activate
        Range("AD1").AutoFilter Field:=30, Criteria1:=month
       Range("B1").AutoFilter Field:=2, Criteria1:=name
       
        countrows = Worksheets("Data").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
       If (countrows <= 1) Then GoTo Nextx
       
       
       datalast = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Offset(2).Row
      
       Set rn = Range("Z2")
       Set rn = Range(rn, rn.End(xlDown)).SpecialCells(xlCellTypeVisible)
       For Each cell In rn
           If cell.Value > 0.9 Then ppercount = ppercount + 1
            Next cell
       
            
       
       Worksheets("Data").ShowAllData
       Worksheets("Sheet1").Cells(x, i).Value = ppercount
       Worksheets("Sheet1").Activate
       ppercount = 0
Nextx:
    Next x
    
Nexti:
    Next i



Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Mike
  • 13
  • 3
  • Probably using `WorksheetFunction.CountIfs`. It doesn't seem like you need to filter. – BigBen Aug 04 '20 at 18:29
  • https://stackoverflow.com/questions/29596432/pointers-needed-for-speeding-up-nested-loop-macro-in-vba/29597193#29597193 –  Aug 04 '20 at 18:30
  • @BigBen I tried to use it but I was not able to get it running. – Mike Aug 05 '20 at 10:13
  • @Mark thanks, but I did not understood -laughing- I'm pretty new to VBA. – Mike Aug 05 '20 at 10:22

0 Answers0