Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Macro to delete repeated rows based on column data (First row to stay)

Posted on 2014-10-07
Medium Priority
250 Views
Column A has repeated data but other corresponding columns have different data.
Thus I can't use excel unique values or remove duplicate values as the from column B to E (Row 2 to 10) the data is repeated.

I only need the first line of the repeating rows.
For example, column A has 5 repeated data (ie. Cell A2 to A6) and the corresponding columns B to E (ie cell B2 to E6) has different values. Desired result: Row 2 only. Row 3 to 6 should be deleted.

Do the same for rest of the data. The first line of the repeating row should stay while the remaining to be deleted.
0
Question by:ceneiqe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2

LVL 27

Expert Comment

ID: 40367671
Perhaps if you could upload a dunny example?
0

LVL 27

Accepted Solution

Glenn Ray earned 1800 total points
ID: 40367685
You CAN use Excel's Remove Duplicates function to achieve this.  Just select Column A in the filters for removal; only the first instance-row will be preserved.

See this example:

Regards,
-Glenn
0

LVL 27

Expert Comment

ID: 40367874
put below code on a module and then run it .  it will do the work for you.

``````Sub RemoveDuplicateRows()
Application.ScreenUpdating = False
Dim ColN As Long
Dim MyS As Worksheet: Set MyS = ActiveSheet
Dim MyR As Range: Set MyR = MyS.Cells(1, 1).CurrentRegion
Dim NumCol As Long: NumCol = MyR.Columns.Count
Dim MyArray As Variant: ReDim MyArray(0 To NumCol - 1)
For ColN = 1 To NumCol
MyArray(ColN - 1) = ColN
Next
Dim rowcount As Long, i As Long, j As Long, k As Boolean
rowcount = MyR.Rows.Count
For i = rowcount To 1 Step -1
k = 0
For j = 1 To NumCol
If MyR.Value2(i, j) <> "" Then
k = 1
Exit For
End If
Next j
If k = 0 Then
MyR.Rows(i).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub
``````
0

LVL 27

Expert Comment

ID: 40368837
ProfessorJimJam, in my test example (seen above), this code did not remove any rows at all. :-(

I think this is because I do not have duplicates across all columns in any row.   The questioner appears to only want rows removed if duplicates appear in the first column only.  The change to the code would be to replace lines 7:11 with
``````MyR.RemoveDuplicates Columns:=1, Header:=xlYes
``````

I do like the extra code to remove blank rows as part of this; nice touch.

Regards,
-Glenn
0

LVL 27

Assisted Solution

ProfessorJimJam earned 200 total points
ID: 40370980
Thanks very much Glenn,

i did not catch the question correctly.   i changed the code as per your point and this is the code that ceneiqe should use.

points also to be given to you Glenn.

``````Sub RemoveDuplicateRows()
Application.ScreenUpdating = False
Dim ColN As Long
Dim MyS As Worksheet: Set MyS = ActiveSheet
Dim MyR As Range: Set MyR = MyS.Cells(1, 1).CurrentRegion
Dim NumCol As Long: NumCol = MyR.Columns.Count
Dim rowcount As Long, i As Long, j As Long, k As Boolean
rowcount = MyR.Rows.Count
For i = rowcount To 1 Step -1
k = 0
For j = 1 To NumCol
If MyR.Value2(i, j) <> "" Then
k = 1
Exit For
End If
Next j
If k = 0 Then
MyR.Rows(i).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub
``````
0

Author Comment

ID: 40372154
Thanks all.

Hi professorjimjam, your code took a while to execute - probably because I have large set of data.

1. There are total of 1077 rows, including header (actually more) but i have previously deleted some manually on my own. The result is there are only 3 rows left.

2. if I use the "Remove Duplicates" method as explained by glenn, the response time is very fast.
and i get the desired result - 191 unique rows (taking the first row of data for each repeating rows). Perhaps i did not select the right column when i tried on the "Remove Duplicates" function in the first place.
0

Author Closing Comment

ID: 40372156
base on the results i received, i assigned the points accordingly.
Hope this is ok.
0

LVL 27

Expert Comment

ID: 40372449
good that it worked for you.

i think all points should have been given to the comment of Glenn.  because his solution worked for you.

but thanks anyways.  my aim in this forum is not really about points, but also to learn from others.

have a good day
0

Author Comment

ID: 40372610
No worries, I gave points to you for effort n prompt reply- really appreciate it. I encounter some experts who just gave one liner or just expect you to know things but in the first place if I know I wouldn't have asked. My questions might seem silly but I admit I am no expert. Then again I always do my best to be clear in my questions. Which is also why Im paying a subscription for the services rendered by the experts and I expect answers.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month5 days, 14 hours left to enroll