Howard said:
Try this on a sample of your data.
Select B1 and run the code.
Change "For x = 1 To 100" to cover your data rows.
Option Explicit
Sub DeleIt()
Dim cell As Range
Dim x As Integer
For x = 1 To 100
If Left(ActiveCell, 7) <> Left(ActiveCell.Offset(0, -1), 7) Then
ActiveCell.Value = ""
End If
ActiveCell.Offset(1, 0).Select
Next
End Sub
This code will run faster if you don't select each cell to work on. Also, I
think GWC probably wants each cell in B compared against *every* cell in A.
So...
Sub DeleIt()
For L0 = 1 To Cells(1, 2).End(xlDown).Row
chk = Left(Cells(L0, 2).Value, 7)
For L1 = 1 To Cells(1, 1).End(xlDown).Row
If (chk) = Left(Cells(L1, 1).Value, 7) Then GoTo okay
Next
Cells(L0, 2).Clear
okay:
Next
End Sub
This clears the contents of the cells, as opposed to deleting the cell and
moving what's underneath it up. If you *want* the cells to be moved up,
then use this instead:
Sub DeleIt2()
For L0 = 1 To Cells(1, 2).End(xlDown).Row
chk = Left(Cells(L0, 2).Value, 7)
If Len(chk) Then
For L1 = 1 To Cells(1, 1).End(xlDown).Row
If (chk) = Left(Cells(L1, 1).Value, 7) Then GoTo okay
Next
Cells(L0, 2).Delete xlShiftUp
L0 = L0 - 1
End If
okay:
Next
End Sub
In both subs, "Cells(1, 1).End(xlDown).Row" can be replaced with 3444 if
that limit isn't going to change (or if this is a one-off sort of thing).
Ditto for "Cells(1, 2).End(xlDown).Row" -> 94422.
