Improve Speed: VBA Code very slow due to changing cell values in for loop

You have

For i = 20 To NUC (=23)

So for the first Loop that results in

Name = Range("O20:O20") '(= Person1)
    'Do something here

On the next Loop, the result is

Name = Range("O20:O21") '(= Person1 and Person2)

On the Loop after that

Name = Range("O20:O22") '(Person1, Person2 and Person3)

On the last Loop you'll get

Name = Range("O20:O23")  '(Person1, Person2, Person3 and Person5)

Is that what you want?
Or do you mean it to be

Name = Cells(i, 15).Value

Originally Posted by jolivanes

Or do you mean it to be

Name = Cells(i, 15).Value

Doh! Thank you - I'm kicking myself for not picking that up!

If you go out of your way and code it as inefficient as you can (many loops), it still is fast enough.

Sub Maybe()
Dim k As Long, a() As String, i As Long, x As String, j As Long, c As Range, y As Double
Application.ScreenUpdating = False
k = 1
ReDim a(1 To k)
a(1) = Cells(20, 7).Value
i = 21
    While Not IsEmpty(Cells(i, 7))
        x = Cells(i, 7).Value
        If IsError(Application.Match(x, a, 0)) Then
            k = k + 1
            ReDim Preserve a(1 To k)
            a(k) = x
        End If
        i = i + 1
    Wend
    For j = LBound(a) To UBound(a)
        y = 0
    For Each c In Range("G20:G" & Cells(Rows.Count, "G").End(xlUp).Row)
        If c.Value = a(j) Then y = y + c.Offset(, -4).Value
    Next c
    If y = 0 Then
    For Each c In Range("G20:G" & Cells(Rows.Count, "G").End(xlUp).Row)
        If c.Value = a(j) Then c.Offset(, -6).Resize(, 7).ClearContents
    Next c
    Else
        Cells(Rows.Count, "P").End(xlUp).Offset(1).Value = y
    End If
    Next j
Application.ScreenUpdating = True
End Sub

Num_Unique_Codes should be

Cells(Rows.Count, "O").End(xlUp).Row

instead of the code you have now.
The same for Numrows

Cells(Rows.Count, 1).End(xlUp).Row

Originally Posted by jolivanes

Num_Unique_Codes should be

Cells(Rows.Count, "O").End(xlUp).Row

instead of the code you have now.
The same for Numrows

Cells(Rows.Count, 1).End(xlUp).Row

Thank you.

Originally Posted by Nick_G

. . . I have the following piece of VBA code: . . .
:
Range("K5") = Application.Sum(Range("C20:C" & Numrows).SpecialCells(xlCellTypeVisible))
:
Range("A20:L" & Numrows).SpecialCells(xlCellTypeVisible).Clear
:
This works 100% perfectly from a functionality point of view, but is very slow. . . .

The most likely way to speed up VBA macros which change cell contents, e.g., assigning values to cells, or clearing their contents, is to begin the macro with

Dim acm As Variant
acm = Application.Calculation
Application.Calculation = xlCalculationManual

and end it with

Application.Calculation = acm

ADDED: Also consider disabling event handlers at the beginning and reenabling them at the end.