Код VBA фильтрует столбец, а затем заполняет формулу для видимых ячеек в другом столбце

Sub Tester()
Dim lastRow As Long

    With ActiveSheet
        .Range("AW2").AutoFilter Field:=2, Criteria1:="Test"
        lastRow = .Range("AW" & Rows.Count).End(xlUp).Row
        .Range(.Range("AZ2"), .Range("AZ" & lastRow)). _
               SpecialCells(xlCellTypeVisible).Value = _
                         "Style Linked to a Dropped T/P"
    End With

End Sub

EDIT: немного обновлен и переработан...

Sub Macro16()

Dim lastRow As Long

    'Insert Column - OK
    ActiveSheet.Columns("AZ:AZ").Insert Shift:=xlToRight, _
                       CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AZ1").Value = "Finalized Comment"

    TagRows "#N/A", "Style Linked to a Dropped T/P"
    TagRows "Confirmed Cost and Missing HTS Code", _
            "Confirmed Cost and Missing HTS Code"
    TagRows "Unconfirmed Cost and HTS Code Present", "Unconfirmed Cost"
    TagRows "Unconfirmed Cost and Missing HTS Code", "Missing HTS Code"

End Sub

Sub TagRows(TextToFind As String, TagWithText As String)
    Dim lastRow As Long
    With ActiveSheet
        'filter the column for "TextToFind"
        .Range("AW:AW").AutoFilter Field:=1, Criteria1:=TextToFind
        'find the last row
        lastRow = .Range("AW" & Rows.Count).End(xlUp).Row
        'if any visible rows, fill in the new comment "TagWithText"
        If lastRow > 2 Then
            .Range(.Range("AZ2"), .Range("AZ" & lastRow)). _
               SpecialCells(xlCellTypeVisible).Value = TagWithText
        End If
        .Range("AW:AW").AutoFilter Field:=1 'clear the filter
    End With
End Sub

Деконструкция метода Range.AutoFilter и обработка строго в оперативной памяти массивы должны ускорить этот процесс.

Option Explicit

Sub tagAZ()
    Dim t As Variant, vFNDs As Variant, vTAGs As Variant
    Dim a As Long, vAWs As Variant, vAZs As Variant

    appTGGL bTGGL:=False

    vFNDs = Array("#N/A", "Confirmed Cost and Missing HTS Code", _
                  "Unconfirmed Cost and HTS Code Present", _
                  "Unconfirmed Cost and Missing HTS Code")
    vTAGs = Array("Style Linked to a Dropped T/P", "Confirmed Cost and Missing HTS Code", _
                  "Unconfirmed Cost", "Missing HTS Code")

    With Worksheets("Sheet1")
        .Columns(52).Insert
        .Cells(1, 52) = "tag comment"
        .Columns(52).ColumnWidth = 32
        With .Range(.Cells(2, 49), .Cells(Rows.Count, 49).End(xlUp))
            vAWs = .Cells.Value2
            ReDim vAZs(LBound(vAWs, 1) To UBound(vAWs, 1), 1 To 1)

            For a = LBound(vAWs, 1) To UBound(vAWs, 1)
                Select Case True
                    'catch True errors
                    Case IsError(vAWs(a, 1))
                        If vAWs(a, 1) = CVErr(xlErrNA) Then _
                            vAZs(a, 1) = vTAGs(0)
                    'catch text-that-looks-like-an-error
                    Case vAWs(a, 1) = vFNDs(0)
                        vAZs(a, 1) = vTAGs(0)
                    'catch the rest
                    Case vAWs(a, 1) = vFNDs(1)
                        vAZs(a, 1) = vTAGs(1)
                    Case vAWs(a, 1) = vFNDs(2)
                        vAZs(a, 1) = vTAGs(2)
                    Case vAWs(a, 1) = vFNDs(3)
                        vAZs(a, 1) = vTAGs(3)
                End Select
            Next a

        End With

        'return processed tag comments to the worksheet
        .Cells(2, 52).Resize(UBound(vAZs, 1), UBound(vAZs, 2)) = vAZs
    End With

    appTGGL

End Sub

Public Sub appTGGL(Optional bTGGL As Boolean = True)
    Debug.Print Timer
    With Application
        .ScreenUpdating = bTGGL
        .EnableEvents = bTGGL
        .DisplayAlerts = bTGGL
        .Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
    End With
End Sub

^{Время, затраченное на 250 тыс. строк случайных данных, где 75 % значений в столбце AW найдут совпадение: 2,06 секунды.} _{Прогон одних и тех же данных через зацикленный .AutoFilter (с отключенными теми же свойствами среды) занял 24,25 секунды.}