thedeadzeds
Active Member
- Joined
- Aug 16, 2011
- Messages
- 451
- Office Version
- 365
- Platform
- Windows
Guys,
Please can someone have a look at the below code. It essentially looks at 12 different tables and removes anything that is not "1st Attempt Made" in column D. Its doesn't seem to work all the time and sometimes leaves 'New Calls', '2nd attempt made; etc in some of the tables.
Sorry if this seems a long way of writing the code but its the only way i could figure it out.
Thanks in advance.
Please can someone have a look at the below code. It essentially looks at 12 different tables and removes anything that is not "1st Attempt Made" in column D. Its doesn't seem to work all the time and sometimes leaves 'New Calls', '2nd attempt made; etc in some of the tables.
Sorry if this seems a long way of writing the code but its the only way i could figure it out.
Thanks in advance.
Code:
Sub Keep_first_attempts()
'DIM
Dim lastRow As Long 'BA Polk Audi Serv & MOT N Calls
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow2 As Long 'BA Kerr Audi Serv & MOT N Calls
lastRow2 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow3 As Long 'CA Polk Audi Serv & MOT N Calls
lastRow3 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow4 As Long 'CA Kerr Audi Serv & MOT N Calls
lastRow4 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow5 As Long 'BAA Polk Audi Serv & MOT N Call
lastRow5 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow6 As Long 'BAA Kerr Audi Serv & MOT N Call
lastRow6 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow7 As Long 'VAG Polk Audi Serv & MOT N Call
lastRow7 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow8 As Long 'VAG Kerr Audi Serv & MOT N Call
lastRow8 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow9 As Long 'BA Campaign New Calls
lastRow9 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow10 As Long 'CA Campaign New Calls
lastRow10 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow11 As Long 'BAA Campaign New Calls
lastRow11 = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastRow12 As Long 'VAG Campaign New Calls
lastRow12 = Cells(Rows.Count, 1).End(xlUp).Row
'TABS
On Error Resume Next
Sheets("BA Polk Audi Serv & MOT N Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("BA Kerr Audi Serv & MOT N Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM3").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow2).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("CA Polk Audi Serv & MOT N Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM6").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow3).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("CA Kerr Audi Serv & MOT N Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM314").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow4).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("BAA Polk Audi Serv & MOT N Call").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM16").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow5).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("BAA Kerr Audi Serv & MOT N Call").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM31417").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow6).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("VAG Polk Audi Serv & MOT N Call").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM1621").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow7).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("VAG Kerr Audi Serv & MOT N Call").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM3141722").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow8).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("BA Campaign New Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM18").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow9).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("CA Campaign New Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM1819").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow10).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("BAA Campaign New Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM181920").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow11).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
On Error Resume Next
Sheets("VAG Campaign New Calls").Select
With Range("A1:A1")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
ActiveSheet.ListObjects("Audi_New_SANDM18192025").Range.AutoFilter field:=4, _
Criteria1:=Array("New Calls", "2nd Attempt made", "3rd Attempt Made"), _
Operator:=xlFilterValues
Range("A2:P" & lastRow12).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
On Error GoTo 0
End With
End Sub