Delete contents of the filtered column with a macro in an excel report

This VBA is to delete all cell data of the filtered column meaning that it is not going to clear everything, only the ones that you have filtered. This macro works with or without a table.

macro excel macro excel macro excel

 

When I use the macro ?

When I have to clear the contents of every cell of the filtered column.

 

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 name by your sheet name
With Worksheets("name")
  ' change 2 by the column ID to be filtered (column B = 2, etc.)
  ' change xx by the value you are searching
  ' to add another filter for another column, i.e. column C, duplicate the line below and change 2 by 3
  .UsedRange.AutoFilter Field:=2, Criteria1:="xx"
  ' change B by your column
  lastRow = .Range("B" & Rows.Count).End(xlUp).Row
  ' if you add column C, change "B" & lastRow by "C" & lastRow
  .Range(.Range("B2"), .Range("B" & lastRow)).SpecialCells(xlCellTypeVisible).ClearContents
  ' optional line below, clear filter to see all data, if you want to use it, delete '
  '.ListObjects(1).AutoFilter.ShowAllData
End With
End Sub              
              

This code should work even if you use a table but if you prefer to put a reference to it, just change “.UsedRange.AutoFilter” by “.ListObjects("Table1").Range.AutoFilter” and modify “Table1” by the name of your table.

Interesting Topics