Convert IF(AND) formula to VBA for convenience

[liamlarmour]

Hi

I am trying to convert the below formula to a VBA code so I can provide to colleagues to use, as they always seem to mess up the formula when assisting me with reports when I am on leave etc.

=IF(AND(COUNTIF(b:b,b2)>1,au2="E Data"),"Delete","")

The formula is entered in Col AV, starting in cell AV2 (as Row 1 contains the headers for each column). Then I am either filtering for the 'Delete' value and deleting rows manually, or using the Ctrl+F in Col AV, 'Look in... Values' and then selecting 'Delete Sheet Rows' from the 'Home>Cells' ribbon (if the formula is left intact, if not, I don't need to change the 'look in' option).

I have had to explain this to some colleagues several times and they seem to always miss a step and delete the wrong records, or just don't get the formula right. Previous to this formula, I was using conditional formatting to highlight duplicates in Col B and then filter to select these 'by colour'; then in Col AU, filter and select 'E Data' and then delete. However as our reports grow in size (more records = more rows), this takes longer to complete as I keep getting the dreaded 'Not Responding' white screen regardless of working on the workbook on my desktop or straight from shared drives etc.

Can someone take me through the process of converting it to VBA, or offer an alternative VBA code?

Conditions:
(1) It would have to apply to an Active Workbook and Active Sheet as the reports I use it on are generated with different names for each depending on the date etc.
(2) The report always has Cols A > AU, with Cols B and AU containing the values I am using to remove rows (records created in error by agents).
(3) There will be an increasing number of rows to search as the reports are cumulative of records created from a certain past date to the current date each time they are run, with several hundred new records created daily.

Any help is much appreciated. My VBA knowledge is extremely basic, so I'm trying to build it up bit by bit. Unfortunately I can't attach the reports as they contain lots of customer data which would need to be anonymised/removed and would just take too long to do.

Thanks