VBA - Remove Duplicates + Sort Data

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,516
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
539
Peter_SSs
Peter_SSs
G
  • Question Question
Stop sort after data is sorted with button
Replies
3
Views
70
Gryder
G
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
508
Micron
M
Sufiyan97
  • Question Question
Add Auto sum in selected blank cells VBA
Replies
4
Views
336
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

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