Clear Visible Contents of a Column is Filtered Range have Data

[ahuang3433]

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=38, Criteria1:= _
        "Reference PjM Comment for PN"

    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=6, Criteria1:= _
        "<>TBD*", Operator:=xlAnd

Hi,

Based on the filter criteria that I have here, I'm trying to create a variable where if there is data return based on the filter criteria to delete visible cell data on Column AL excluding the header row. If there is no data return, skip.

 

[Sequoyah]

Hi ahuang3433
try adding this line to your code

Range("AL2:AL" & Range("AL" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).ClearContents

 

[ahuang3433]

Hi Sequoyah,

The code you provided only works when there is data visible after filtering.

 

[ahuang3433]

Bumping this up.

 

[Fluff]

How about

With ActiveSheet.ListObjects("Table2")
   .Range.AutoFilter Field:=38, Criteria1:="Reference PjM Comment for PN"
   .Range.AutoFilter Field:=6, Criteria1:="<>TBD*", Operator:=xlAnd
   If .ListColumns(38).Range.SpecialCells(xlVisible).Count > 1 Then
      .ListColumns(38).DataBodyRange.ClearContents
   End If
End With

 

[ahuang3433]

Thanks! It Worked Well.

 

[Fluff]

You're welcome & thanks for the feedback.