Macro autofilter not working, as expected

[Mr2017]

Hi

I recorded a macro which applies a filter to row 1 of a sheet, then filters data by a specific criteria ie "On Deal."

However, when I add a new set of data to that sheet and run the macro, it doesn't work as expected.

Can someone please advise how I should amend the code below? The range I've selected will always be less than 10,000 rows, hence the reason I've selected that range.

Thanks in advance.

Sub autofiltertest

Rows("1:1").Select
Selection.autofilter
ActiveSheet.Range("$A$1:$CG$10000").autofilter Field:=4, Criteria1:= _
"On Deal "

End Sub

 

[zuriqi]

i had the same issue before , so what i did was deleting the previous filtered data after modifying the list and run macro again

 

[Fluff]

Please define

it doesn't work as expected.

 

[Mr2017]

Hmmm...that's what I was thinking of doing....

 

[Mr2017]

When I apply the filter, it correctly selects the rows "On Deal." However, I then delete specific columns and then copy the data (assuming that'll just copy the rows that have been filtered. But having just stepped through it, it appears that after I've deleted unnecessary columns, the filter is reset - is this normal?)

Sub RevT()


Application.ScreenUpdating = False


Sheet11.UsedRange.ClearContents


'copy all lines from the line level tab
Sheet1.Activate
Range(Range("A4"), Range("CG4").End(xlDown)).Select
Selection.Copy
Sheet11.Activate
Range("A1").PasteSpecial (xlPasteValues)
Range("A1").PasteSpecial (xlPasteFormats)


' apply auto filter and filter by lines on promo


    Rows("1:1").Select
    Selection.autofilter
    ActiveSheet.Range("$A$1:$CG$10000").autofilter Field:=4, Criteria1:= _
        "On Deal"
        


Range("D:E, I:K, N:N, R:T, X:Y, AA:CD").Select
Selection.Delete


Selection.CurrentRegion.Select
Selection.Copy


Sheet4.Activate
Range("b4").PasteSpecial (xlPasteValues)
Columns("B:S").AutoFit


Application.ScreenUpdating = True
End Sub

 

[Fluff]

You're deleting the column that you have filtered, which is why it's being reset.

 

[Mr2017]

Ok. If I changed the filter to "Not On Deal" do you know if it would be possible to delete all the rows "Not On Deal" without having to define a new variable?

 

[Fluff]

You haven't defined any variables!
Why not just copy the data & then delete the columns you don't want?

 

[Mr2017]

*I meant to say without having to define any variables.

I've written this to delete the data that is "Not On Deal" and it works, but it takes out the title row!

Do you know how I can retain the title row, if it starts in row 1, please?

Sub RevT()

Application.ScreenUpdating = False

Sheet11.UsedRange.ClearContents

Sheet1.Activate
Range(Range("A4"), Range("CG4").End(xlDown)).Select
Selection.Copy
Sheet11.Activate
Range("A1").PasteSpecial (xlPasteValues)
Range("A1").PasteSpecial (xlPasteFormats)

        With ActiveSheet.Range("A1").CurrentRegion
            .autofilter 4, "Not On Deal "
            .Offset(1, 0).SpecialCells (xlCellTypeVisible)
            .EntireRow.Delete
            .autofilter
        End With

Range("e:E, I:K, N:N, R:T, X:Y, AA:CD").Select
Selection.Delete

Selection.CurrentRegion.Select
Selection.Copy

Sheet4.Activate
Range("b4").PasteSpecial (xlPasteValues)
Columns("B:S").AutoFit

Application.ScreenUpdating = True
End Sub

 

[Mr2017]

I've done a bit of trial and error and amended the code so the filter and delete section looks like this, and it's working as intended, now.

But thank you for pointing out that the column with the filter was getting deleted. There were several columns to delete, and that part of the code was written afterwards, so it was an oversight!

Thanks again!

        With ActiveSheet.Range("A1").CurrentRegion
            .autofilter 4, "Not On Promotion"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
            .autofilter
        End With