What do these lines of code do in AutoFilter macro

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
I'm trying to understand my own macro (created with generous help from MrExcel posters) turning on and off lines of code that confuse me. I am left with the following questions, if anyone can help me understand I'll never have to wonder about these again.

VBA Code:
Set h = Sheets("Sheet 1")
    Application.GoTo ActiveSheet.Range("A1"), True
    If h.AutoFilterMode Then h.AutoFilterMode = False   '<<< Why is this not AutoFilterMode = TRUE ?????
    lr = Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With h.Range("A6:L" & lr)
    
    .AutoFilter Field:=2, Criteria1:="=ABC" & strName & "*", Operator:=xlAnd
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=2  '<<< Why do I need these lines ???????
        
    .AutoFilter Field:=12, Criteria1:="=123" & strName & "*", Operator:=xlAnd
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=12  '<<< Why do I need these lines ???????

    .AutoFilter Field:=12, Criteria1:="=XYZ" & strName & "*", Operator:=xlAnd
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=12  '<<< Why do I need these lines ???????

    h.AutoFilterMode = False

Thanks in advance

hip
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See comments

VBA Code:
Set h = Sheets("Sheet 1")  'From here on h means Sheets("Sheet 1")
    Application.GoTo ActiveSheet.Range("A1"), True  'Same as Range("A1").Seledt
    If h.AutoFilterMode Then h.AutoFilterMode = False   'Clears any filters sthat are set.
    lr = Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row   'Defines last row of data as an integer
    With h.Range("A6:L" & lr)
   
    .AutoFilter Field:=2, Criteria1:="=ABC" & strName & "*", Operator:=xlAnd
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=2  'deletes rows for any cells in Column A containing the filter criteria starting at row 7
       
    .AutoFilter Field:=12, Criteria1:="=123" & strName & "*", Operator:=xlAnd
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=12  'deletes rows that meet filter criteria in column L starting ar row 7

    .AutoFilter Field:=12, Criteria1:="=XYZ" & strName & "*", Operator:=xlAnd
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=12  'deletes rows that meet filter criteria in column L starting at row 7

    h.AutoFilterMode = False  'turns off auto filter
As to why you need to delete the rows, only you can answer that question. But I would assume that whoever wrote the code based it on some parameter that was laid out in the original requirement to create the code. Could be to avoid alert messages when new data is entered into those same ranges of cells.
 
Solution

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
Thank you again, seems you are my guardian angel these past few weeks.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,521
Messages
5,625,295
Members
416,087
Latest member
drth

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