VBA Code to Highlight/ Delete only Visible Rows of Filtered Results

[lynnydd76]

Hi Excel Masters,


I am working with a spreadsheet that does a data dump every month that is really messy, I'm trying to automate as much of the cleanup as possible.


The only thing I'm stuck on with the VBA code is how to delete visible rows instead of specific rows as the number of rows with data will change from month to month.


Any help would be much appreciated.

Sub Delete_Visible_Rows
'
'
    Cells.Select
    Selection.AutoFilter
    Range("A3").Select
    ActiveSheet.Range("$A$1:$J$2373").AutoFilter Field:=1, Criteria1:=Array( _
        "------------------*" _
        , _
        "- - *" _
        , _
        "========*" _
        , _
        "=======*" _
        , _
        "ACCNT*" _
        , "C CTR*", "COMPANY", "PROD   PROJ"), Operator _
        :=xlFilterValues
    Rows("5:2371").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData
    Range("A1").Select
End Sub

Sub Highlight_Visible_Rows()
'


    Cells.Select
    Selection.AutoFilter
    Range("A3").Select
    ActiveSheet.Range("$A$1:$J$3000").AutoFilter Field:=2, Criteria1:="=ATT :" _
        , Operator:=xlOr, Criteria2:="=CA"
    Rows("6:1857").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10498160
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.ShowAllData
    Range("A1").Select
End Sub

 

[Michael M]

Maybe this UNTESTED

Sub Delete_Visible_Rows()
Dim myrange As Range
Application.ScreenUpdating = False
Set myrange = ActiveSheet.UsedRange
    With myrange
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:=Array( _
        "------------------*" _
        , _
        "- - *" _
        , _
        "========*" _
        , _
        "=======*" _
        , _
        "ACCNT*" _
        , "C CTR*", "COMPANY", "PROD   PROJ"), Operator _
        :=xlFilterValues
    End With
myrange.Resize(myrange.Rows.Count - 1, 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.ShowAllData
Range("A1").Select
myrange.AutoFilter
Application.ScreenUpdating = True
End Sub