itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Filter a particular criteria then delete the selection using a macro in an excel report

This macro helps me to filter a specific criteria then to delete all rows of the selection, it doesn’t matter if the data is inside a table or not.

macro excel macro excel
macro excel macro excel

 

When I use the macro ?

To delete all rows in which the criteria is appearing.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is/are the macro(s) ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.


Sub test()
' change 1 by your column ID (i.e. column A = 1) and change xxx by your criteria
Range("A1").AutoFilter Field:=1, Criteria1:="xxx"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True
On Error Resume Next
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
On Error GoTo 0
End Sub

Interesting Topics