VBA: Find Columns and Delete Cells

[gd6noob]

Hi there,

I had a couple queries.

I have sheet that requires to delete some rows. I have 2 headers (which are never in same columns) that I need to find and then delete rows. The Number of rows also varies each day.

1. Approval Status - Delete all rows with status that does not equal "Pending"
2. Created - Delete all rows with dates that today and future dates. Only dates allowed are older then today.

Book1
	A	B	C	D	E	F	G
1	Header 1	Header 2	Approval Status	Created	Header 5	Header 6	Header 7
2			Approved	3/1/2022
3			Rejected	3/16/2022
4			Pending	1/1/2022
Sheet1

Thank you for any help.

 

[gordsky]

Maybe this. I have assumed you want it actually deleting rather than filtering as you said delete and that there are no data that would fulfill BOTH criteria

Dim ws As Worksheet
Dim MyApp As Long, MyCtd As Long
Dim myrng As Range
Dim mydte As Date


Set ws = Sheets("Sheet1") 'change to be your sheetname
Set myrng = ws.UsedRange
mydte = Date
Debug.Print myrng.address
Debug.Print mydte

 MyApp = myrng.Find(what:="*Approval*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
 MyCtd = myrng.Find(what:="*Created*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

 ws.UsedRange.AutoFilter Field:=MyApp, Criteria1:="<>Pending", Operator:=xlAnd, Field:=MyCtd, Criteria2:=">" & Date
 
' Approval Status - Delete all rows with status that does not equal "Pending"
' Created - Delete all rows with dates that today and future dates. Only dates allowed are older then today.
 
 ws.UsedRange.SpecialCells(xlVisible).EntireRow.Delete
 
 ws.AutoFilterMode = False

 

[gd6noob]

Maybe this. I have assumed you want it actually deleting rather than filtering as you said delete and that there are no data that would fulfill BOTH criteria

Dim ws As Worksheet
Dim MyApp As Long, MyCtd As Long
Dim myrng As Range
Dim mydte As Date


Set ws = Sheets("Sheet1") 'change to be your sheetname
Set myrng = ws.UsedRange
mydte = Date
Debug.Print myrng.address
Debug.Print mydte

 MyApp = myrng.Find(what:="*Approval*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
 MyCtd = myrng.Find(what:="*Created*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

 ws.UsedRange.AutoFilter Field:=MyApp, Criteria1:="<>Pending", Operator:=xlAnd, Field:=MyCtd, Criteria2:=">" & Date
 
' Approval Status - Delete all rows with status that does not equal "Pending"
' Created - Delete all rows with dates that today and future dates. Only dates allowed are older then today.
 
 ws.UsedRange.SpecialCells(xlVisible).EntireRow.Delete
 
 ws.AutoFilterMode = False

you are correct, I would need the rows deleted.

For some reason when I tried your code, I get an "Delete method of Range class failed"

 

[kevin9999]

Another option...
EDITED

Sub Filter_Delete()
    Dim i As Long, j As Long
    i = WorksheetFunction.Match("Approval Status", Rows(1), 0)
    j = WorksheetFunction.Match("Created", Rows(1), 0)
    
    With Range("A1").CurrentRegion
        .AutoFilter i, "<>Pending"
        .Offset(1).EntireRow.Delete
        .AutoFilter
        .AutoFilter j, ">=" & CLng(Date)
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[gordsky]

you are correct, I would need the rows deleted.

For some reason when I tried your code, I get an "Delete method of Range class failed"

this line

 ws.UsedRange.SpecialCells(xlVisible).EntireRow.Delete

should be

 ws.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[gd6noob]

Thank you @gordsky and @kevin9999, both methods works.

 

[kevin9999]

Thank you @gordsky and @kevin9999, both methods works.

Glad we could help and thanks for the feedback ?