Macro looping

Hi all,

I'm using the following Macro to delete all rows that have a cell containing USD in a column range from F6 to F5000. The macro works but it only deletes a row at a time when run. Can anyone tell me how get this to loop through all rows until the end?

Thanks (Excel 2003)

Sub test()
Dim Last_Row As Long, n As Long

Application.ScreenUpdating = False
    

Last_Row = Range("F6:F5000").End(xlDown).Row

For n = 5 To Last_Row
    If Cells(n, 6).Value = USD Then
        Cells(n, 6).EntireRow.Hidden = False
    Else: Cells(n, 6).EntireRow.Hidden = True
    End If
Next n
        
Application.ScreenUpdating = True

End Sub

Check out this example

Delete rows on criteria

Click the Reset button to populate Column A

stjohnsmythe
when hiding rows it best to loop in reverse order so the row numbers not hidden

Option Explicit
Sub test()
    Dim nRow As Long
    Application.ScreenUpdating = False
    For nRow = Cells(Rows.Count, 6).End(xlUp).Row + 1 To 6 Step -1
        If Not Cells(nRow, 6).Value = "USD" Then
            Cells(nRow, 6).EntireRow.Hidden = True
        End If
    Next
    Application.ScreenUpdating = True
End Sub

I would use AutoFilter, automate it with VBA if necessary

Roy has suggested the correct approach

Option Explicit
Sub test()
    Dim nRow As Long
    Application.ScreenUpdating = False
    For nRow = Cells(Rows.Count, 6).End(xlUp).Row + 1 To 6 Step -1
        If Cells(nRow, 6).Value = "USD" Then
            Cells(nRow, 6).EntireRow.Hidden = True
        End If
    Next
    Application.ScreenUpdating = True
End Sub

I had trouble posting back yesterday.

I would actually automate AutoFilter to do this. Dave has an example in his workbook, but it needs some changes.

The button won't work because there is no data to filter on the page containing the button. The sheet to work with needs referring to in the code. Also, ScreenUpdating needs switching back on at the end, I usually add an error handler to make sure.

Sub DeleteRwsWithAutoFilter()
    With Sheet2
        'by Dave Morrison
        On Error GoTo err_exit
        'timer not really necessary
        '  .Range("K1") = Format(Now, "h:mm:ss.0000")
        Application.ScreenUpdating = False
        .Range("A1").AutoFilter Field:=1, Criteria1:="a"
        'delete visible Rows, this is where an error could occur
        'using Specialcells will error if no Specialcells are found
        .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range("A1").AutoFilter
        .Range("L1") = Format(Now, "h:mm:ss.0000")
        '     .Range("M1") = Format(Range("L1") - Range("K1"), "h:mm:ss.0000")
err_exit:
        On Error GoTo 0
        Application.ScreenUpdating = False
    End With
End Sub