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
 
Sort by date Sort by votes

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
In that case if any column is empty, row2 should show 0 & this line
Code: 
If Cells(2, i) > 8 Then
should then skip that column.
 
Upvote 0
Got it.

I later changed the formula from

This
COUNTA(A4:A1000)

To
=IF(COUNTA(A4:A1000)=0,"0",COUNTA(A4:A1000))


and this was causing the problem


Now i will stick to the previous formula

All working fine now..


Thanks for you help as always.

Regards,
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0
Hi Fluff,

One more thing but with different data format - if possible

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][th]
O​
[/th][th]
P​
[/th][th]
Q​
[/th][th]
R​
[/th][th]
S​
[/th][th]
T​
[/th][th]
U​
[/th][th]
V​
[/th][th]
W​
[/th][th]
X​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#D9D9D9]
SIZE WEIGHT - TOTAL PIECES CHECKED > 54​
[/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][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][td=bgcolor:#D9D9D9]
​
[/td][td=bgcolor:#D9D9D9]
​
[/td][td=bgcolor:#D9D9D9]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
6​
[/td][td]
​
[/td][td]
​
[/td][td]
48​
[/td][td]
​
[/td][td]
​
[/td][td]
0​
[/td][td]
​
[/td][td]
​
[/td][td]
0​
[/td][td]
​
[/td][td]
​
[/td][td]
0​
[/td][td]
​
[/td][td]
​
[/td][td]
0​
[/td][td]
​
[/td][td]
​
[/td][td]
0​
[/td][td]
​
[/td][td]
​
[/td][td]
0​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
[ Article 1 ]​
[/td][td]
​
[/td][td]
​
[/td][td]
[ Article 2 ]​
[/td][td]
​
[/td][td]
​
[/td][td]
[ Article 3 ]​
[/td][td]
​
[/td][td]
​
[/td][td]
[ Article 4 ]​
[/td][td]
​
[/td][td]
​
[/td][td]
[ Article 5 ]​
[/td][td]
​
[/td][td]
​
[/td][td]
[ Article 6 ]​
[/td][td]
​
[/td][td]
​
[/td][td]
[ Article 7 ]​
[/td][td]
​
[/td][td]
​
[/td][td]
[ Article 8 ]​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
1 x 1​
[/td][td]
​
[/td][td]
​
[/td][td]
2 x 2​
[/td][td]
​
[/td][td]
​
[/td][td]
3 x 3​
[/td][td]
​
[/td][td]
​
[/td][td]
4 x 4​
[/td][td]
​
[/td][td]
​
[/td][td]
5 x 5​
[/td][td]
​
[/td][td]
​
[/td][td]
6 x 6​
[/td][td]
​
[/td][td]
​
[/td][td]
7 x 7​
[/td][td]
​
[/td][td]
​
[/td][td]
8 x 8​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
50​
[/td][td]
100​
[/td][td]
50 x 100​
[/td][td]
50​
[/td][td]
101​
[/td][td]
50 x 101​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
50​
[/td][td]
101​
[/td][td]
50 x 101​
[/td][td]
51​
[/td][td]
101​
[/td][td]
51 x 101​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
50​
[/td][td]
102​
[/td][td]
50 x 102​
[/td][td]
50​
[/td][td]
100​
[/td][td]
50 x 100​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
51​
[/td][td]
100​
[/td][td]
51 x 100​
[/td][td]
50​
[/td][td]
102​
[/td][td]
50 x 102​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
51​
[/td][td]
101​
[/td][td]
51 x 101​
[/td][td]
50​
[/td][td]
101​
[/td][td]
50 x 101​
[/td][td][/td][td][/td][td]
x​
[/td][td][/td][td][/td][td]
x​
[/td][td][/td][td][/td][td]
x​
[/td][td][/td][td][/td][td]
x​
[/td][td][/td][td][/td][td]
x​
[/td][td][/td][td][/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
51​
[/td][td]
102​
[/td][td]
51 x 102​
[/td][td]
51​
[/td][td]
100​
[/td][td]
51 x 100​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td][/td][td]
x​
[/td][td]
50​
[/td][td]
101​
[/td][td]
50 x 101​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td][/td][td]
x​
[/td][td]
50​
[/td][td]
100​
[/td][td]
50 x 100​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td][/td][td]
x​
[/td][td]
50​
[/td][td]
101​
[/td][td]
50 x 101​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td][/td][td][/td][td]
x​
[/td][td]
51​
[/td][td]
100​
[/td][td]
51 x 100​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td][/td][td][/td][td]
x​
[/td][td]
50​
[/td][td]
102​
[/td][td]
50 x 102​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][td]
​
[/td][td]
​
[/td][td]
x​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: SIZE[/td][/tr][/table]
[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]




In this case I would want the code to look at the Columns C, F, I and so on till Column X to remove duplicates and sort data if more than 8 entries are there in each column.

EDIT:
The table above is so messy. I am not able to paste it properly. I dont know why ?
 
Last edited:
Upvote 0
In that case change the loop to
Code: 
For i = 3 To 24 Step 3
 
Upvote 0
Changes in red

Code: 
Sub hrayani()   
   
   Dim i As Long
   
[B][COLOR=#ff0000]   For i = 3 To 24 Step 3 [/COLOR][/B]
      If Cells(2, i) > 8 Then
         With Cells([B][COLOR=#ff0000]5[/COLOR][/B], i).Resize(Cells(2, i))
            .removeduplicates 1, xlNo
            .sort key1:=Cells([B][COLOR=#ff0000]5[/COLOR][/B], i), order1:=xlAscending, Header:=xlNo
         End With
      End If
   Next i
   
   
   
End Sub

Not working.....

In case if the table is not clear.

1) Data Starts from row # 5
2) Formula in C2 =COUNTIF(C5:C1000,"<> x ")
3) Formula in C5 till C100 =A5&" x "&B5
4) Manual entries in cell A5 & B5


EDIT
Please wait
 
Last edited:
Upvote 0
Hi Fluff,

I having following two issues

1) The code is removing duplicates but also deleting formulas from Column C.
As soon as the duplicates are removed and the cells are shifted up - it clears the the cell formula too.

2) Its not sorting data



Can we look at columns A & B to remove duplicates ?

In this way the formula in column C will remain in tact
 
Upvote 0
Using RemoveDuplicates on multiple columns is flaky, so I would advise against doing that.
You could convert the formula to values first, but I don't understand what you are trying to do, as you are only removing the duplicates in individual columns, not the entire data.
 
Upvote 0
Using RemoveDuplicates on multiple columns is flaky, so I would advise against doing that.
Click to expand...

Yes, I agree

Here is what I have come up with and the code does exactly what I want to


Code: 
Sub duplicatesremove()


    If Range("C2").Value > 8 Then
    ActiveSheet.Range("A5:B1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
    
[B][COLOR=#ff0000]    With ActiveSheet.sort[/COLOR][/B]
[B][COLOR=#ff0000]     .SortFields.Add Key:=Range("A5"), Order:=xlAscending[/COLOR][/B]
[B][COLOR=#ff0000]     .SortFields.Add Key:=Range("B5"), Order:=xlAscending[/COLOR][/B]
[B][COLOR=#ff0000]     .SetRange Range("A5:B1000")[/COLOR][/B]
[B][COLOR=#ff0000]     .Header = xlNo[/COLOR][/B]
[B][COLOR=#ff0000]     .Apply[/COLOR][/B]
[B][COLOR=#ff0000]    [/COLOR][/B]
[B][COLOR=#ff0000]    End With[/COLOR][/B]
    End If
    
    
    If Range("F2").Value > 8 Then
    ActiveSheet.Range("D5:E1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
    End If
        
        If Range("I2").Value > 8 Then
        ActiveSheet.Range("G5:H1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
        End If
            
            If Range("L2").Value > 8 Then
            ActiveSheet.Range("J5:K1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
            End If
                
                If Range("O2").Value > 8 Then
                ActiveSheet.Range("M5:N1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
                End If
                    
                    If Range("R2").Value > 8 Then
                    ActiveSheet.Range("P5:Q1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
                    End If
                        
                        If Range("U2").Value > 8 Then
                        ActiveSheet.Range("S5:T1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
                        End If
                            
                            If Range("X2").Value > 8 Then
                            ActiveSheet.Range("V5:W1000").removeduplicates Columns:=Array(1, 2), Header:=xlNo
                            End If
                      
End Sub

Is there a better what of writing it ? ... I am sure there must be
secondly I want the red part of the code i.e. the sorting after after every set or removing duplicates.

Regards
 
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
4
Views
79
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
515
Micron
M
Sufiyan97
  • Question Question
Add Auto sum in selected blank cells VBA
Replies
4
Views
336
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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