Macro to remove all orders with 'XXX' status, then remove all order types besides: Z , L,-CodePudding

We have a monthly process of manually exporting information from SAP to Excel and then creating a production schedule from it. One of the first steps when getting the information to excel is filtering down to certain criteria and removing the rest. I am a begginer with VBA/Macros, and I can only find examples online on how to delete rows via certain criteria instead of deleting rows outside of my desired criteria..

I want to remove all orders with 'XXX' status. Then I want to remove all order types EXCEPT: Z, L, ZR

I am assuming this is possible, and that I just am not educated enough on vba to figure it out quite yet.. Let me know, thanks!

CodePudding user response：

Apply the filter for the ones you want to keep and copy them to a new workbook or a temporary sheet that you can copy back from.

Option Explicit

Sub Macro1()

    Const COL_STATUS = 1 ' column no
    Const COL_ORDERTYPE = 4 ' column no
       
    Dim ws As Worksheet, wsTemp As Worksheet
    Dim rng As Range
       
    With ThisWorkbook
        Set ws = .Sheets("Sheet1")
        Set wsTemp = .Sheets.Add(after:=.Sheets(.Sheets.Count))
    End With
       
    Application.ScreenUpdating = False
    With ws.UsedRange
        .AutoFilter COL_STATUS, Array("Z", "L", "ZR"), xlFilterValues
        .AutoFilter COL_ORDERTYPE, "<>ZZZ"
        .Copy wsTemp.Range("A1")
        .AutoFilter
        .Cells.Clear
    End With
       
    With wsTemp
        .UsedRange.Copy ws.Range("A1")
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    Application.ScreenUpdating = True
    MsgBox "Done"
  
End Sub