AutoFilter to delete data begin with multiple certain value

Hazel 0
Viewed 44

I have created a PowerQuery Report. And I need to export 3 different excel files every week and save them in the same folder, and the data columns positions are different in these 3 weekly files. After I export the data, I also need to delete the lines based on the cell value of a column. I'm doing it by putting a filter in that column to hide the lines with cell value begin with "VR", "VP", "LR" & "LP" (The lines begin with "VR", "VP", "LR" & "LP" are the lines I need to keep). Then delete all the visible lines.

I'm writing a VBA code to delete those lines. Below is my code but it didn't filter anything when I run the code. Can someone please give me somoe suggestion to make it work? Many thanks

ActiveWorkbook.ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=7, Criteria1:=Array("<>VR*", "<>VP*", "<>LR*", "<>LP*"), Operator:=xlAnd
powerquery vba excel
2 Answers

You should use a helper column plus a list of the values to be excluded:

enter image description here

The formula for your helper column: =COUNTIF(configExclude[exclude type],$B2)

Using a separate list for the types to be excluded makes the solution easier to adjust - in case your boss wants you to exlcude more or less types :-)

Your Autofilter-code will then check the helper column for 0-values.

useryPy710

I found an alternative by using loop. But it would be great if someone can tell me if it's possible to do it with autofilter

Sub delete()

Dim a As Long

For a = Sheet3.UsedRange.Rows.Count To 2 Step -1

If Left(Sheet3.Cells(a, "D").Value, 2) = "VR" Then
ElseIf Left(Sheet3.Cells(a, "D").Value, 2) = "LR" Then
ElseIf Left(Sheet3.Cells(a, "D").Value, 2) = "VP" Then
ElseIf Left(Sheet3.Cells(a, "D").Value, 2) = "LP" Then
Else
Rows(a).delete
End If

Next a

End Sub
Hazel0
Related