Trying to figure out how to tweak the below VBA code to delete duplicates. The current code deletes data which is duplicated in column B.
However, I need certain conditions met ie value in column B(Security) is duplicate, then look at Column D(Lad) and delete "DFL (if its in the column)
Any ideas please?
thanks
.
Option Explicit
Sub DeleteDuplicates1()
Dim x As Long
Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
For x = LastRow To 5 Step -1
If Application.WorksheetFunction.CountIf(Range("B5:B" & x), Range("B" & x).Text) > 1 Then
Range("B" & x).EntireRow.DELETE
End If
Next x
End Sub
However, I need certain conditions met ie value in column B(Security) is duplicate, then look at Column D(Lad) and delete "DFL (if its in the column)
Any ideas please?
thanks
.
Option Explicit
Sub DeleteDuplicates1()
Dim x As Long
Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
For x = LastRow To 5 Step -1
If Application.WorksheetFunction.CountIf(Range("B5:B" & x), Range("B" & x).Text) > 1 Then
Range("B" & x).EntireRow.DELETE
End If
Next x
End Sub
Time | Security | Description | Lad |
Close | 2001119 | AAR CORP COM USD1 | DFL |
Close | 2001119 | AAR CORP COM USD1 | BID |
Close | 2037062 | HELIX ENERGY SOLUTIONS GROUP | DFL |
Close | 2037062 | HELIX ENERGY SOLUTIONS GROUP | BID |
Close | 2108601 | BOEING CO,CMN US$5 | DFL |
Close | 2108601 | BOEING CO,CMN US$5 | BID |
Close | 2109723 | BOMBARDIER INC 'B' SUB VTG NPV | DFL |
Close | 2109723 | BOMBARDIER INC 'B' SUB VTG NPV | BID |
Close | 2130109 | ONEOK INC COM NPV | DFL |
Close | 2130109 | ONEOK INC COM NPV | BID |
Close | 2182531 | CORE LABORATORIES N.V. EUR0.04 | DFL |
Close | 2182531 | CORE LABORATORIES N.V. EUR0.04 | BID |
Close | 2186072 | CALLON PETROLEUM CO USD0.01 | DFL |
Close | 2186072 | CALLON PETROLEUM CO USD0.01 | BID |
Close | 2186254 | CENTURY ALUMINUM COMPANY | DFL |
Close | 2186254 | CENTURY ALUMINUM COMPANY | BID |
Close | 2387109 | GUESS COM US$0.01 | DFL |
Close | 2387109 | GUESS COM US$0.01 | BID |
Close | 2405302 | HALLIBURTON CO COM US$2.50 | DFL |
Close | 2405302 | HALLIBURTON CO COM US$2.50 | BID |
Close | 2408044 | HAWAIIAN HOLDINGS INC | DFL |
Close | 2408044 | HAWAIIAN HOLDINGS INC | BID |