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

ABennett757

New Member
Joined
Mar 25, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Do you only have one table on each sheet?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

A better version in-case you don't have any blank cells
VBA Code:
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
 
Solution

ABennett757

New Member
Joined
Mar 25, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
A better version in-case you don't have any blank cells
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top