VBA - Remove Duplicates + Sort Data

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,539
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

Here is the sample data


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]
Article # 1​
[/TD]
[TD]
Article # 2​
[/TD]
[TD]
Article # 3​
[/TD]
[TD]
Article # 4​
[/TD]
[TD]
Article # 5​
[/TD]
[TD]
Article # 6​
[/TD]
[TD]
Article # 7​
[/TD]
[TD]
Article # 8​
[/TD]
[TD]
Article # 9​
[/TD]
[TD]
Article # 10​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]
1​
[/TD]
[TD]
256​
[/TD]
[TD]
25​
[/TD]
[TD]
256​
[/TD]
[TD]
256​
[/TD]
[TD]
256​
[/TD]
[TD]
256​
[/TD]
[TD]
256​
[/TD]
[TD]
256​
[/TD]
[TD]
256​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]
2​
[/TD]
[TD]
265​
[/TD]
[TD]
256​
[/TD]
[TD]
265​
[/TD]
[TD]
265​
[/TD]
[TD]
901​
[/TD]
[TD]
289​
[/TD]
[TD]
369​
[/TD]
[TD]
289​
[/TD]
[TD]
369​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]
10​
[/TD]
[TD]
298​
[/TD]
[TD]
265​
[/TD]
[TD]
289​
[/TD]
[TD]
289​
[/TD]
[TD]
925​
[/TD]
[TD]
298​
[/TD]
[TD]
901​
[/TD]
[TD]
298​
[/TD]
[TD]
901​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]
256​
[/TD]
[TD]
1237​
[/TD]
[TD]
298​
[/TD]
[TD]
298​
[/TD]
[TD]
298​
[/TD]
[TD]
987​
[/TD]
[TD]
365​
[/TD]
[TD]
925​
[/TD]
[TD]
365​
[/TD]
[TD]
925​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD]
289​
[/TD]
[TD]
2365​
[/TD]
[TD]
1237​
[/TD]
[TD]
365​
[/TD]
[TD]
365​
[/TD]
[TD]
2365​
[/TD]
[TD]
369​
[/TD]
[TD]
936​
[/TD]
[TD]
369​
[/TD]
[TD]
936​
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A2[/TH]
[TD="align: left"]=COUNTA(A4:A1000)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I am using below macros to remove duplicates and sort data

Code: 
Sub removeduplicates()

  ActiveSheet.Range("A4:B1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("B4:B1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("C4:C1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("D4:D1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("E4:E1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("F4:F1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("G4:G1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("H4:H1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("I4:I1000").removeduplicates Columns:=1, Header:=xlNo
  ActiveSheet.Range("J4:J1000").removeduplicates Columns:=1, Header:=xlNo
  
End Sub

Code: 
Sub sort()    
    Range("A4:A1000").sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo
    Range("B4:B1000").sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlNo
    Range("C4:C1000").sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo
    Range("D4:D1000").sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlNo
    Range("E4:E1000").sort Key1:=Range("E4"), Order1:=xlAscending, Header:=xlNo
    Range("F4:F1000").sort Key1:=Range("F4"), Order1:=xlAscending, Header:=xlNo
    Range("G4:G1000").sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlNo
    Range("H4:H1000").sort Key1:=Range("H4"), Order1:=xlAscending, Header:=xlNo
    Range("I4:I1000").sort Key1:=Range("I4"), Order1:=xlAscending, Header:=xlNo
    Range("J4:J1000").sort Key1:=Range("J4"), Order1:=xlAscending, Header:=xlNo
    
End Sub


Is there a better way to write the macro.

Further I would like following 2 conditions if possible for both macros.

1) Starting from row # 4 look at the last used cell in each column rather than define range in macro
2) Both macros should remove data & sort data only if more than 8 cells have data in there.

Any help would be appreciated.

Regards,

Humayun
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
How about
Code: 
Sub hrayani()
   Dim i As Long
   
   For i = 1 To 10
      If Cells(2, i) > 8 Then
         With Cells(4, i).Resize(Cells(2, i))
            .RemoveDuplicates 1, xlNo
            .Sort key1:=Cells(i, 4), order1:=xlAscending, Header:=xlNo
         End With
      End If
   Next i
End Sub
 
Upvote 0
Hi Fluff,

Thanks for the reply... but it is not working.

Run time error 1004
The sort reference is not valid. Make sure that it's within the data you
want to sort, and and the first sort by box isn't the same or blank.

this line of the code is highlited
Code: 
 .sort key1:=Cells(i, 4), order1:=xlAscending, Header:=xlNo
 
Upvote 0
Oops got it the wrong way round, it should be
Code: 
.sort key1:=Cells([COLOR=#ff0000]4, i[/COLOR]), order1:=xlAscending, Header:=xlNo
 
Upvote 0
Works Fine .... Thanks

I want two seperate macros so can use like this... I tried and it works but just wanted you to have a look at it

Code: 
Sub removeduplicates()   
Dim i As Long
   
   For i = 1 To 10
      If Cells(2, i) > 8 Then
         With Cells(4, i).Resize(Cells(2, i))
            .removeduplicates 1, xlNo


         End With
      End If
   Next i
End Sub


Code: 
Sub sort()   
Dim i As Long
   
   For i = 1 To 10
      If Cells(2, i) > 8 Then
         With Cells(4, i).Resize(Cells(2, i))


            .sort key1:=Cells(4, i), order1:=xlAscending, Header:=xlNo
         End With
      End If
   Next i
End Sub
 
Last edited:
Upvote 0
Thanks fluff,

Just noticed one thing that if any column is empty then the code does not run
Like it telling me that there has to be some value in every column

It gives error message

Run time error 1004
Application defined or object defined error
 
Upvote 0
Which line of code gives the error?
 
Upvote 0
Does the formula in row 2 show the correct number of values for each column?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Trying to do an Auto Sort when I enter Tab using VBA Code
2
Replies
19
Views
566
Peter_SSs
Peter_SSs
G
  • Question Question
Stop sort after data is sorted with button
Replies
6
Views
161
Peter_SSs
Peter_SSs
I
  • Solved
Sort Columns low to high but im told cells are merged
2 3
Replies
23
Views
1K
6StringJazzer
6StringJazzer
E
  • Solved
Custom Sorting in VBA
Replies
3
Views
620
Micron
M
Sufiyan97
  • Question Question
Add Auto sum in selected blank cells VBA
Replies
4
Views
366
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top