Deleting rows with partially redundant data

Noah14 · #1 09-18-2014, 06:12 AM

Dear pros,

I have got a table with a couple thousand of line entries and a number of columns. One of the columns contains document names (one per line, example pls. see below). For purposes of further data processing, I consider all document names with the same 21 leading digits (so everything up to and including the last dash) as identical. I am looking for a vba code that deletes identical / redundant lines and only keeps the lines with document name ending on 01. In the example below, lines 1 and 4 are to be kept and 2,3,5,6 deleted.

Thanks in advance!
Noah

322-215000-02-1-1-PR-01
322-215000-02-1-1-PR-02
322-215000-02-1-1-PR-03
322-215000-02-1-2-PR-01
322-215000-02-1-2-PR-02
322-215000-02-1-2-PR-03

gmayor · #2 09-19-2014, 11:46 PM

Assuming Mac VBA is similar to PC VBA then the following should work. The code assumes column A contains the document names in question

Code:

Sub RemoveDuplicates()
Dim LastRow As Long
Dim i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
    If Left(Cells(i, 1), 18) = Left(Cells(i - 1, 1), 18) Then
        Rows(i).Delete Shift:=xlUp
    End If
Next i
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Deleting rows & pushing them up causing issues	ep2002	Excel	2	08-29-2014 02:09 AM
Macro for Column to Rows Data	brunssl2	Excel Programming	3	04-28-2014 07:07 AM
Deleting rows with specific criteria	joflow21	Excel	9	11-22-2013 12:10 PM
Outlook 2010 & Redundant Files Cleanup	goombarok	Outlook	0	03-09-2012 08:04 AM
Concatenated data in subsequent rows	doorsgirl	Excel	4	09-15-2011 10:37 PM