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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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