VBA code to filter table and clear column 'A' if column 'B' is empty

[ABennett757]

Hi, I am looking for a way to apply a filter to only leave blank cells in a column and then clear the contents in a different column that are left in the filtered column. The column with my filter criteria corresponds to an item quantity so the idea is to remove all unused items from a default list of about 300 items and then turn the filter off so I am left with a table that only has items with a quantity. I recorded a Macro by completing the filter and column select/clear steps manually and it produced the VBA code below which works as intended. However, this code has the name of the table being filtered ("Table1") hard-coded. I need to copy this worksheet within the same workbook and thus the tables in subsequent copies will have a different name so the Macro no longer works for any copied sheets. So, I'm looking for a way to do this without hard coding the table name (or potentially have the table name as a variable and pull in the name from the active table). Thanks in advance for any replies!

Sub TestFilter()
'
' TestFilter Macro
'
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="="
Range("Table1[ID '#]").Select
Selection.ClearContents
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6
End Sub

 

[Fluff]

Do you only have one table on each sheet?

 

[ABennett757]

Do you only have one table on each sheet?

Yes, there will only be one table on each sheet.

 

[Fluff]

Ok, how about

Sub TestFilter()
'
' TestFilter Macro
'
With ActiveSheet.ListObjects(1)
   .Range.AutoFilter Field:=6, Criteria1:="="
   .ListColumns("ID #").DataBodyRange.ClearContents
   .Range.AutoFilter Field:=6
End With
End Sub

 

[Fluff]

A better version in-case you don't have any blank cells

Sub TestFilter()
'
' TestFilter Macro
'
With ActiveSheet.ListObjects(1)
   .Range.AutoFilter Field:=6, Criteria1:="="
   On Error Resume Next
   .ListColumns("ID #").DataBodyRange.SpecialCells(xlVisible).ClearContents
   On Error GoTo 0
   .Range.AutoFilter Field:=6
End With
End Sub

 

[ABennett757]

A better version in-case you don't have any blank cells

Sub TestFilter()
'
' TestFilter Macro
'
With ActiveSheet.ListObjects(1)
   .Range.AutoFilter Field:=6, Criteria1:="="
   On Error Resume Next
   .ListColumns("ID #").DataBodyRange.SpecialCells(xlVisible).ClearContents
   On Error GoTo 0
   .Range.AutoFilter Field:=6
End With
End Sub

Works like a charm. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.