Filter and delete visible cells next row

[Karthik Thandapani]

Hi,

I have a requirement to filter a column and delete visible cells from the column next. Can someone please see the below code and help;

Filter column P, criteria = "Not Completed" and delete visible cells from column Q.

Worksheets("Sheet1").Range("P1").AutoFilter Field:=1, Criteria1:="Not Completed"
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True

Please see the attached file.

Thanks!

 

[Luke M]

I assume you meant to delete the entire row, as I'm not sure how you would delete a single cell from a filtered list...

Sub DeleteVisibleCells()
Application.ScreenUpdating = False
With Worksheets("Sheet1")
    .Range("P1").AutoFilter Field:=1, Criteria1:="Not Completed"
    Application.DisplayAlerts = False
    .AutoFilter.Range.Offset(1, 0).EntireRow.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    .Range("P1").AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[Karthik Thandapani]

@Luke M : the above provided code deletes data from column 'P' too. But can you please tweek that a bit to only delete/clear data from column 'Q' only. Thanks

 

[Luke M]

Ah, you just want to clear the cell, not delete it. Change this line:

.AutoFilter.Range.Offset(1, 0).EntireRow.SpecialCells(xlCellTypeVisible).Delete

to this:

Intersect(.AutoFilter.Range.Offset(1,0),.Range("Q:Q")).SpecialCells(xlCellTypeVisible).ClearContents

To make whole thing:

Sub DeleteVisibleCells()

Application.ScreenUpdating = False
With Worksheets("Sheet1")
    .Range("P1").AutoFilter Field:=1, Criteria1:="Not Completed"
    Application.DisplayAlerts = False
    Intersect(.AutoFilter.Range.Offset(1, 0), .Range("Q:Q")).SpecialCells(xlCellTypeVisible).ClearContents
    Application.DisplayAlerts = True
    .Range("P1").AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[Karthik Thandapani]

Thanks a lot @Luke M . It works like a breeze!

 

[Karthik Thandapani]

@Luke M : a quick question. Will this won't work with data tables?
When I tried applying the above code to tables, it's not working. Please help

 

[Luke M]

Yes and no. Won't work as is, but can modify. Worksheets can have an Autofilter, and Each table can have an Autofilter. So, need to limit our range callouts a little bit. Here's the new structure, make changes where needed.

Sub DeleteVisibleCells()
Dim FilterCell As Range
Application.ScreenUpdating = False
With Worksheets("Sheet1")
    'Use this as a anchor point
    Set FilterCell = .Range("P1")
    FilterCell.AutoFilter Field:=1, Criteria1:="Not Completed"
    Application.DisplayAlerts = False
    'Make sure table name is correct
    Intersect(FilterCell.Offset(0, 1).EntireColumn, .ListObjects("Table1").DataBodyRange).SpecialCells(xlCellTypeVisible).ClearContents
    Application.DisplayAlerts = True
    .Range("P1").AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[Karthik Thandapani]

@Luke M : In case you want to run code in activesheet & .ListObjects(1) will work?
Please correct the below code as it is throwing up error at Intersect(filterCell) line.

Dim FilterCell As Range
Application.ScreenUpdating = False
With ActiveSheet
    'Use this as a anchor point
  Set FilterCell = .Range("P20")
    FilterCell.AutoFilter Field:=1, Criteria1:="Not Completed"
    Application.DisplayAlerts = False
    'Make sure table name is correct
  Intersect(FilterCell.Offset(0, 2).EntireColumn, .ListObjects(1).DataBodyRange).SpecialCells(xlCellTypeVisible).ClearContents
    Application.DisplayAlerts = True
    .Range("P20").AutoFilter
End With
Application.ScreenUpdating = True

 

[Luke M]

The .ListObjects(1) would refer to the first table in the sheet. As long as there's at least 1 table in the sheet, AND column Q intersects that table, you should be good.