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

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.
ceneiqeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
Perhaps if you could upload a dunny example?
0
Glenn RayExcel VBA DeveloperCommented:
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:
remove duplicates -single column selection
Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamCommented:
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
    MyR.RemoveDuplicates Columns:=(MyArray), Header:=xlYes
    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

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Glenn RayExcel VBA DeveloperCommented:
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

Open in new window


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

Regards,
-Glenn
0
ProfessorJimJamCommented:
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
     MyR.RemoveDuplicates Columns:=1, Header:=xlYes
    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

Open in new window

0
ceneiqeAuthor Commented:
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
ceneiqeAuthor Commented:
base on the results i received, i assigned the points accordingly.
Hope this is ok.
0
ProfessorJimJamCommented:
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
ceneiqeAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.