What do these lines of code do in AutoFilter macro

hip2b2

Board Regular
Joined
May 5, 2003
Messages
135
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
Solution
Thank you again, seems you are my guardian angel these past few weeks.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top