Macro autofilter not working, as expected

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 
Last edited:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

zuriqi

Board Regular
Joined
Dec 8, 2008
Messages
79
i had the same issue before , so what i did was deleting the previous filtered data after modifying the list and run macro again
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hmmm...that's what I was thinking of doing....
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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?)

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
You're deleting the column that you have filtered, which is why it's being reset.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
You haven't defined any variables!
Why not just copy the data & then delete the columns you don't want?
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
*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?

Code:
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
 
Last edited:

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!

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

Watch MrExcel Video

Forum statistics

Threads
1,123,142
Messages
5,599,971
Members
414,353
Latest member
ljhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top