Hi guys,
I'm looking to create a macro to automatically filter out any data from one tab that matches any values in my Mids tab Column C. To do this, I have used a countif function to work out if there is a match or not then filter out the true values. Once done, I use some other VBA code to delete any hidden rows.
I did this manually by recording the macro but when I re-runit it did not do the same thing.
All my data disappears, filters are applied to the headersand Column C (Compass?) has the Header as an option to filter by.
I’m needing this to also be dynamic as the number of rows tobe assessed will vary.
Coding I have is below. Any help you could give me would be greatly appreciated!
Sub Compass_Remove_2()
'
' Compass_Remove_2 Macro
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "Compass?"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(Mids!R2C3:R804C3,RC2)>0"
Range("C2").Select
Selection.FillDown
ActiveSheet.Range("$A$1:$Z$106").AutoFilter Field:=3, Criteria1:="FALSE"
End Sub
I'm looking to create a macro to automatically filter out any data from one tab that matches any values in my Mids tab Column C. To do this, I have used a countif function to work out if there is a match or not then filter out the true values. Once done, I use some other VBA code to delete any hidden rows.
I did this manually by recording the macro but when I re-runit it did not do the same thing.
All my data disappears, filters are applied to the headersand Column C (Compass?) has the Header as an option to filter by.
I’m needing this to also be dynamic as the number of rows tobe assessed will vary.
Coding I have is below. Any help you could give me would be greatly appreciated!
Sub Compass_Remove_2()
'
' Compass_Remove_2 Macro
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "Compass?"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(Mids!R2C3:R804C3,RC2)>0"
Range("C2").Select
Selection.FillDown
ActiveSheet.Range("$A$1:$Z$106").AutoFilter Field:=3, Criteria1:="FALSE"
End Sub
