Remove duplicates in multiple column

A

alxvin

New Member
Joined
Jul 5, 2014
Messages
7
I got more than 1000 columns. Hence, removing duplicates in each column is time consuming. Could anyone suggests a VBA code that can solve this problem? Thanks in advance.

Raw Data

Header1Header2Header3Header4
4825141253474565456
48251217896547456545612
48251123545456
45621123545
412341545
4123123
4123
4123

<tbody>
</tbody>

Expected outcome

Header1Header2Header3Header4
4825141253474565456
4562121789654545612
41231235123
415

<tbody>
</tbody>
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Not working, still i have duplicates. Only the rows having the duplicates are removed.
 
Upvote 0
Ok. Try this
Code: 
Sub removeDuplicates()
Dim xrange As Range, cell As Range
finalrow = Cells(Rows.Count, 1).End(xlUp).row
finalcolumn = Cells(1, Columns.Count).End(xlToLeft).column
Set xrange = Range(Cells(1, 1), Cells(finalrow, finalcolumn))
For Each cell In xrange
    If Application.CountIf(xrange, cell.Value) > 1 Then
        With cell
            cell.Delete
        End With
    End If
Next
End Sub
 
Upvote 0
This is my sample data
Signal TransductionImmune SystemMetabolismDiseaseAdaptive Immune SystemInnate Immune SystemHemostasisSignaling by GPCRMetabolism of lipids and lipoproteins
ESR1PRKCGGLP1RPRKCGCD247PRKCGPRKCGPRKCGHADHA
PRKCGIL18NAMPTTGFBR1RAP1BCD247ATP2B2PRKCHHADHB
TGFBR1CD247OGDHTGFBR2RAP1APRKCAPRKCHGLP1RGNPAT
TGFBR2NCAM1GNPATNAMPTIRS2PYCARDATP2B1OR13G1SMPD1
PRKCHPRKCAPDHXCD247CREB1PRKCECAP1PRKCASHD
GLP1RPIAS1PRKCAMIB1ITGB2IFNB1SELPPTGFRLTA4H
PRKCAPRKCEURODPRKCAIRS1PRKCDPRKCAOPRD1ALOX5
PRKCEIFNB1LTA4HPRKCECALRRPS6KA1RAP1BPRKCEPRKAB2
PRKCDPRKCDALOX5PRKCDSYKIRS2PRKCEPRKCDESRRA
MST1IL1AESRRAIRS2RELARPS6KA2IFNB1HRH2MED23
HRH2RPS6KA1XDHCREB1HLA-DOBCREB1PRKCDTACR1MED24
PDE3BIRS2AGPAT1IRS1IGLV11-55ITGB2RAP1APDE3BAGPAT1
RPS6KA1RPS6KA2MTTPCALRRPS27AIRS1PDE3BRPS6KA1PDSS1
IRS2IRS1DGAT2NHLRC1RNF123RPS6KA5TP53RPS6KA2MTTP
RPS6KA2ITGB2UGCGHPSEEGFSYKCALUSHHABCC3
TP53CALRMAOBNPPASTIM1RPS6KA3ITGB2PTCH1FABP6
IRS1RPS6KA5MAOAABCC1TRAF6JUNPPBPCREB1CYP46A1
SYKSYKSLC19A1MYCCD79ARELATMSB4XPPBPABCC1
RPS6KA5RPS6KA3CYP2R1SLC9A1TRAF2IGLV11-55ESAMDGKBUGCG
RPS6KA3JUNTXNSMAD4SELLLIMK1DGKBDGKADGAT2
LTB4RRELAEP300NOTCH1NFKBIARPS27AF8RPS6KA3EP300
JUNHLA-DOBGSTO1EP300ZAP70EP300DGKALTB4RCYP2R1
TCF7L2LIMK1EHD3RPS27AIFITM3TRAF3SYKADCYAP1MED13
MYCTRAF3POT1SLC19A1FOXO3EGFATP2A3MCF2STAR
RELASTIM1CLOCKEGFFOXO4DEFB104AATP2A2NPSR1CYP2J2
PYYDEFB104AINPP4BHSPA1BFCGR3ATRAF6MYBPYYEP300
TRAF6EIF4G2INPP4ADHFRDYNC1H1TXNLAMP2PDE4DSLCO1A2
PDE4DIL1BMVKHSPA1ACANXATF1EP300PDE4AABCB4
PDE4ATRAF6SLC6A8EP300CD274EP300HBG2CYSLTR1ACE
ATF1TXNPKD1CCL1MICBATF2STIM1CYSLTR2LTC4S
CYSLTR1ATF1A2MGSTO1MICATRAF2EGFCCL3NRF1
COL4A2EP300STK11AAASGRAP2NFKBIASELECCL2POT1
TRAF2ATF2AQP1HES5HLA-DMATLR10EP300RGS2LPCAT2
EP300TRAF2PPP1CAPSEN2PTENDHX9SELLCCL7CLOCK
CYSLTR2TLR10ENO2MMACHCMAP3K8GZMMPRKAR2BCCL5INPP4B
SHC2DHX9CHKAPRKAR2BCXADRPRKAR2BF13BCCL4INPP4A
RGS2GZMMIVDSMARCC1UBA1IRAK1EHD3PTGER4MVK
SHC3ZAP70INSIG2ST3GAL3CASP9IRAK2PRKAR2AADMPKD1
HES5CFHR1HMGCRPRKAR2AKITLGCFHR1PRKAR2BPRKAR2BA2M
PTGER4CFHR3PLD1PRKAR2BFOXO1PRKAR2ACD177PRKAR2APPP1CA
PSEN2FOXO3INSIG1PPP1R3CPDGFRBPRKAR2BA2MMC4RCHKA
MC4RFOXO4PTENFOXO3FOXO3CFHR3KLK3PRKAR2BHMGCR
GZMBCANXPDHBSLC2A4IGLV8-61FOXO3CLUCNR2INSIG2
FOXO3CD274SLC22A3FOXO4PDGFRAFOXO4SLC16A1CNR1CYP17A1
FOXO4AGERGSTZ1SLC2A1HLA-AFCGR3AFLNAUTS2RCYP11B1
UTS2RGRAP2P4HBSLC2A2RAPGEF3LGALS3ATP2B2EDNRBPLD1
A2MPTENGSTM2SMAD7HLA-CAGERTBXA2RPPP1CACYP11B2
STK11HLA-DMAGSTM1H2AFXHLA-BGRAP2PTGIREDNRAINSIG1
EDNRBBCL2L1ARNTLCANXSHC1PTENCXADRPTGER3DPEP1
PPP1CAIL3RAPTGES3ABCA4CALM1BCL2L1CAPZA1PTGER2PTEN
EDNRACXADRHPGDPPP1CARAF1PIN1ACTN4TBXA2RABCA1
FSTL1KITLGFMO3ENO2FOXO4CAPZA1F13A1GIPBMP1
PTGER3IGLV8-61PPAP2BNCSTNEGFCASP8ANGPT1PENKPTGIS
PTGER2RAPGEF3RAPGEF3PYGMCDKN1ACASP9F2RANXA1PRKAA2
EIF4EBP1ELMO1LDLRSLC25A5KLCASP4TIMP1HRH1P4HB
GRAP2CALM1ABHD5PTENCDKN1BKITLGRAPGEF3PTGIRMED1
XIAPRAF1PLCB1STRAPPDIA3FOXO1PECAM1KALRNARNTL
PTENEGFG6PDGIFHLA-DQB1IGLV8-61SHC1CXCR3ALOX5
GRB10HLA-DQB1G6PCCAMPLNPEPPDGFRBANGPT4KALRNPTGES3
PENKPDIA3CALM1USP9XNRASFOXO3CALM1CXCR4HPGD
THBS2NRASFMN1CXCR4HLA-DRB1CASP1PROCAPLNPPAP2B
HRH1HLA-DRB1DEGS1CASP9TNFRSF14PDGFRARAF1CXCR6GPX1
USP9XTNFAIP3SLC37A4SMAD4HLA-DRB5CASP2CFDGASTSLC25A20
KALRNHLA-DRB5GPD1LSMAD3ERBB4HLA-CANGPT2F2RLDLR
APLNEGFRMTRSMAD2EGFRHLA-BEGFS1PR3ABHD5
GASTTSC2SLCO1A2KITLGKITMYD88PLEKCXCL9ME1
KITLGIGKV2D-29PPARACST3TSC2ELMO1SELPLGS1PR2DEGS1
S1PR3CAP1SPHK1FOXO1IGKV2D-29C4BPASPARCS1PR1EBP
S1PR2CTSSSPHK2SMAD7PIK3R1SHC1NRASCXCL6GPD1L
RAPGEF3MDM2CYP46A1PDGFRBPIK3R2CALM1SERPINA1CXCL5SRD5A1
HSD17B6RASGRP2CYCSFOXO3CTSSCFDGAS6OPRD1HMGCR
S1PR1HMGB1RAPGEF3PDGFRAPDPK1FOXO4VEGFACXCL2AGT
CHN2RAPGEF3APOC2NUP214MDM2RAF1GATA1PLCB1COQ2
VANGL2TREX1APOC3HLA-ARASGRP2CDKN1AGATA2MCF2LIPE
LDLRKEAP1TCN2HSD17B6RAPGEF3EGFSIRPACALM1COQ7
PLCB1TRIB3LRP2LDLRKEAP1CFIKCNMB1RAF1COQ6
VANGL2DNM3FLAD1G6PDPDGFBCDKN1BCAP1ROCK2PPARA
PCSK5DNM1FXNELMO1PDGFAKLPIK3R1TAC1ACSL1
CALM1PLD2SLCO1B1G6PCFGF5CFHFGBNRASSLCO1A2
RAF1TANKNOS3SHC1MRC1NRASPIK3R2EGFRPIK3R1
EGFITPR1TRIB3CALM1FGF7ERBB4FGAAGTSPHK1
IGF1RPLD1HMOX2FOXO4TRIB3TNFAIP3PDPK1HTR2APIK3R2
ROCK2PIK3CANDUFB8RAF1FGF1EGFRPLGGLP1RSPHK2
HIST4H4IL6DSEXRCC4FGF2TSC2GATA3PIK3R1PLG
NRASIFNA1PLD2CDKN1AFGF3KITTLN1PIK3R2CYP46A1
DKK1IFNA2ITPR1EGFFGF4IGKV2D-29RASGRP2PDPK1CYP1B1
EGFRRAC1GLUD1GAPDHSDNM3SIRPAGATA4MMP3TEAD2
TSC2DCTN5PLD1CDKN1BDNM1CAP1MMP1MAP2K2APOC2
CDC73MAPK8PIK3CAFMN1ITPR1MAP2K6RAPGEF3RASGRP2APOC3
MDM2HLA-DQA1SLC5A2KLPIK3CAPIK3R1GATA6MAP2K1LRP2
RASGRP2STAT3BCHERBL1GRAP2PIK3R2LRP8CX3CL1SLCO1B1
TLN1GRB2GSTP1GOT1VCAM1CTSSTEKSAA1PTGDS
MMP3IFNGSLC5A4HIST4H4RELPDPK1FGGPTAFRTRIB3
RAPGEF3MAP3K8PIK3CGDLL4CXADRMDM2SERPINB2AVPPLD2
LEPRAP1S2EPHX2NRASTRIM63MAP2K2PLAURCXCL1PLD1
TFDP1CD59HMOX1SLC37A4RAC1RASGRP2PF4V1ARHGEF3PIK3CA
CX3CL1FGF23SLC5A1SLC25A16DCTN5MAP2K1IGF2F2RL1BCHE
APOC2CD55ALDH7A1MTRDNM3HMGB1PDGFBPTGDRPLTP
APOC3TRPC1SACM1LHEY1MALMAP2K4SERPINB6CCL27PIK3CG
ATP6V1B1DOCK1ABCG2HEY2CYBATREX1IGF1CCL25CUBN
LRP8NRG1ABCG5ERBB4DNM2SAA1PDGFACCL23EPHX2
LRP6TANKGALNSEGFRCYBBPDGFBGUCY1A3CCL20ACHE
MMP9LATPTGS1KITDNM1PDGFANOS3C5AR1GLA
TERTMAP3K1SCARB1TSC2HLA-DQA1FGF5KCNMA1CCL21ACAT1
AVPFLGPTGS2ADAM10MAPKAP1TLR1GLG1ADORA2BASAH2
LRP5IL6STFDFT1IDUAGRB2FGF7NOS1ADORA2ATBXAS1
LRP2TRIM37CHPT1PIK3R1MAP3K8TRIB3ITPR1ITPR1SACM1L
ARHGEF3PSME1CELFGAAP1S2TLR3SOD1F2RL3CYP21A2
LRP1NFKB2ACACAPIK3R2CDC34FGF1F2RL3PIK3CAPTGES
IGF2NFKB1SNAP25PDPK1FGF23TLR2PIK3CALPAR3CREBBP
IGF1BCL2NCANADAM12TRPC1FGF2SLC7A9LPAR4NES
TRIB3LBPACACBMDM2NRG1TLR5HRGLPAR1GGT1
DNM3CLEC5AAKR1B1MAP2K2PLCG1ACTR2TFPILPAR2ABCG5
TFDP2CTSKSULT1A1ADAM10LATFGF3PIK3CGGNRH1SULT2A1
DNAL4HLA-EMVKMAP2K1FLGTLR4CDK2PIK3CGPTGS1
DNM1ICAM1SULT1A3CDKN2BFGF9FGF4CDK5LPAR5SCARB1
ADORA2BHLA-GDDCTFDP1PSME1TLR7SRGNADCY1PTGS2
ITPR1TXNIPSULT1A2APOC2TRIM37TLR6IFNA1ADCY7FDFT1
ADORA2ACD4IDH3BAPOC3NFKB1DNM3CXADREMR1ME1
PIK3CACTSSELOVL6LRP8PTPN11TLR9IFNA2CCL19CHPT1
LPAR3ICAM2MDH1ATP6V1B1IRS1TLR8LAT2CDK5COQ7
CCND1ICAM3NCOA6SAA1ATG7DNM1MAPK3CCL17ACACA
LPAR4PRKAR1AAKR1C1BGNIRS2PLD2PPIACCL11CEL
ATP6V1C1IL6RTAZTCN2SOCS3TANKRAC1MAPK3ACACB
LPAR1PRLTCN2PSENENPSMD13ITPR1CREBBPRAC1ABCA1
LPAR2CD74PDB1LRP2SOCS1PLD1JMJD1CMMP10AKR1B1
PIK3CGIKBKBTCN1FLAD1CSKPIK3CAGP1BBAKAP13MVK
GNRH1CTSCAKR1C2LRP1VASPNOD2F13BCALCRLMTM1
LPAR5CTSBPSME1PDGFBCTSKNOD1GP1BAMAPK1SPHK1
RAC1CTSDAKR1C4PDGFATUBA1BGRAP2COL1A2GRB2COQ2
MAPK8FOSGPIFGF5HLA-EABI2COL1A1C3AR1GC
MMP10NFATC1CETPNOS3ICAM1ABI1MAPK1VIPR1CYP2C9
AKAP13IKBKGTATFGF7HLA-GRELPRKCZROM1NCOA6
FKBP1ACTSASGPP1TRIB3CTSSADCY1ITGA5GPR39CYP2C8
DLL4DOCK1PNLIPRP2COASYCD4ADCY7ITGA2TRPC6SREBF1
CALCRLLAP3ELOVL2FGF1ICAM2NFATC1TFPIF2RELOVL6
PRKCZCIITADCNFGF2ICAM3NFATC3GRB2GNALNFYA
STAT3CD8ALCATFGF3CD200DEFA1SPNADRBK1NFYB
GRB2FCGR1AGNB3FGF4CD74IFNA1TRPC6ADORA3NCOA6
VIPR1IL7RDDCNOS1LNPEPMAPK8F2RGHRLFAS
ROM1BIRC3ELOVL4GTF2A1NR4A1IFNA2MAFKCD55SP1
SMARCA4PSMD13SAT1HMGA1IKBKBMAPK9MAGHTR4AKR1C1
TRPC6CD86MLXIPLTFDP2CTSCLAT2JAM3GHRHTAZ
F2RCD14GNASDSECTSBMAPK3HBBTRPC3AKR1C2
GNALISG20ELOVL6ITPR1CTSDRAC1THPOADORA1ANGPTL4
COL5A1CD81GPTPIK3CACTSACREBBPHBDADCYAP1R1PPARG
COL5A2LRRFIP1FDPSSLC5A2IKBKGMALDOCK1ADCYAP1R1AKR1C4
ATP6V1E1CD80HDCADAM17PTPRCDNM3TRPC3CRHFADS2
FGF23LY96NDUFA5SLC5A4LAP3CTNNB1PLAURPS6KA2PIK3C2A
HTR4CD19DLDATP6V1C1CD8ADNM2PLCG1MMP3ESRRA
CD55MYH2TPMTCUBNL1CAMMAPK8LATAVPR2CETP
FURINCD14CYP27B1ADCY1FCGR1ADNM1FN1KISS1PLA2G4A
ADORA3IFNA17PRKAR1AADCY7CD86MAPKAP1PLATGPR39CYP27A1
RIT2SQSTM1GSTT1IDSPSMD13MAPK1PLA2G4APLA2G4AFADS1
CHEK1FCGR2ABLVRACHMP2ACDC42CFIVWFRHOASGPP1
NRG1PSMB7CBSSLC5A1PAG1GRB2RHOARAMP2ELOVL2
TRPC3PRKCAGPD1MAPK3CD81C4APTPN11RAMP3PNLIPRP2
ADORA1FGF19CTSARAC1CTLA4C4BGNB3GNB3HSPG2
FN1ERBB4HBA1PPIACD80CD59GNASTAS2R50LCAT
ADCYAP1R1ERBB3RXRACREBBPCD19ADRBK1CD63GNASELOVL4
PLATNCF4ALOX5APMALHSPA5CD55SERPINC1MLNRELOVL6
MMP3INPP5DCYP2C19CYBAPSMB7FGF23CD9CCR4LRP2
FLGERBB2ODC1CYBBFGF19HSP90B1BSGNPYFDPS
GFAPNCF1PSMD13DLL4ERBB4DOCK1JMJD1CCCRL2ABCC3
AVPR2MYH9NRF1FKBP1AINPP5DCRPCSKCASRCHAT
AKR1C1NCF2HSD17B10PDCD6IPNCF4NRG1VCLCCKALB
KISS1MAP2K4CATGALNSERBB3TANKSERPINE1ADRA1DCYP27B1
TAZPRKCECAV1MAPKAP1NCF1PLCG1TUBA1BMTNR1AAPOA4
PSME1PTENTYMSMAPK1ERBB2LATALBPRKAR1AAPOA2
AKR1C4VHLGPC1PRKCZNCF2RPS6KA2PRKAR1AEDN3APOA1
GPR39PPP3R1TYMPSTAT3PAG1MAP3K1CD2CX3CR1PNLIP
NFKB1IFNA21ADRA2APCK2VHLFLGF12EDN1CYP1A1
FNTALY86GPC5FBLPTENFGF9F11EDN2LTC4S
RAMP2FGF23ADRA2CGRB2ITGA4NFKB2F10GIPRSDC1
RHOAFCGR3AARSASNAP25FGF23NFKB1APOA1TRPC6CYP1A2
RAMP3PSMA5NPC1ACACAPPP2CAATG5SERPINF2DGKALCAT
SPP1CD28ITPKCACACBPSMA5PTPN11SH2B3GPBAR1GPD1
GNB3PSMA6ARSBNCANFCGR3AIRS1SH2B1DGKBCTSA
RHODPSMA2ENPP1AP1S2PSMA6IRS2JAK2POMCRXRA
GNASCYLDENO3NOTCH1CD28BCL2TGFB1GALLPL
E2F1HLA-DPB1NQO1ADRBK1ICOSLBPVEGFBOPRK1ALOX5AP
CCR4HBEGFENO2ATP6V1E1PSMA2CLEC5AGP9ADRB1POMC
E2F2MAPK10PSMB7HSPA1BHLA-DPB1C1RTGFB2ADRB2CYP2C19
COL9A3MAPK14CADFURINHBEGFCTSKVEGFCCDC42APOA5
NPYOASLPRKCAHSPA1AFGF4C1SSLC8A1ADRB3GAL
SERPINE1FGF20CACNA1EFGF23FGF5ARPC3GP5BDKRB2NRF1
CASRCD36INPP5DNRG1C3HLA-EDOCK1BDKRB1CAV1
CCKPSMD9MBOAT7MDH1ITGB1VTNGP6P2RY12CUBN
ADRA1DMAPKAPK2TPH1FASFGF2TXNIPLRRC16AP2RY13FASN
MTNR1AHRASGPC5SP1TUBB3CTSSTRPC6P2RY14LPA
EDN3FGFR3GSTZ1PLCG1ITGALCD4DGKAFPR1OXCT1
PRKAR1AFGFR4CYP24A1BANF1TUBB2APRKAR1AFIGFFPR2ARSA
PRLFGFR1MLYCDRPEITGAVNR4A1DGKBVAV3NPC1
EDN1FGFR2ARSHFLGCD207S100A12ABL1GHSRARSB
IKBKBTECCA3FGF9ITGB2IKBKBL1CAMADRA2APNPLA2
EDN2PSMD4CA6AMY1AFGF20MEF2CSERPINE2ADRA2CINPP5D
VEGFBZBP1PSMA5AKR1C1CD36STAT6SERPINE1ADRA2BMBOAT7
FCER2CISHBCKDHBSNCAAKT1CTSBACTBP2RY6CYP7A1
RBL1FCN1PPARGC1ATAZPSMD9MEF2ACDC42CXCL12MTTP
MYCBLMHPSMA6TCN2SRCFOSP2RY12CXCL11HSD11B1
VEGFCFCN2CA1TCN1KIR3DL1IKBKGCAV1CXCL10MAPKAPK2
ARHGDIAFCN3CYP27A1PSME1HRASNFATC1ANGPT1CALCRARSH
NFATC1CCR2PSMA2AKR1C4FGFR3DOCK1TLN1PRKCAMLYCD
TRPC6LCKMTHFD1FNTAFGFR4MASP2VAV3CXCL16CYP24A1
FIGFCD34CPT1BMFGE8FGFR1CASP10ADRA2AP2RY1ALOX15B
GPBAR1FADDTBL1XR1GPIFGFR2MASP1ADRA2CCRHR1PPARGC1A
HIF1ARIPK1FASNRHOAPSMD4ABL1ADRA2BPLCB1ARSA
GZMBAAASCPT1AHSPG2BLMHARPC1APDE5ATIAM1CYP27A1
HEY1MEFVMTAPDCNBLKFCGR1ATHBDPRKCHCYP4F2
CD86RIPK3MTHFRIRS1TRAT1BIRC3HSPA5HTR1DCPT1B
PSMD13RIPK2SULT1E1SAT1CD34CD86TTNSSTTBL1XR1
ADRB1S100BALADPTPN11LCKWASF1IFNA17HTR1FFASN
SERPINE1GSK3BCD36EIF2AK2PTPN6ACTBPRKCAHTR1BPRKAG2
ADRB2SQSTM1PSMD9IRS2PDK1CD14P2RY1PRKCECPT1A
HEY2KIR2DS2CACNB2E2F2UBE2J1CDC42INPP5DHTR1ASREBF1
ADRB3CD40MAPKAPK2PSMD13B2MMBL2PRKCHGHSRSREBF2
TLN1CD46DCNLRP2PDCD1VAV3OLR1PTHPLA2G5
GPC1CDKN1ATHCSKRIPK2CD80PRKCEIAPPANKRD1
ADRA2ACD44PSMD4SERPINE1GSK3BLRRFIP1ITGA5PPP3R1CD36
GPC5RAP1BCHRM3SSRP1CD40LGCD19THBS1PPP2CANPC1L1
ADRA2CPYCARDABCG1CNGB1KIR2DS2LY96GATA6GNALPLA2G3
CD80RAP1ADGAT2HPSE2CD40MYH2PTPN1DHHPLA2G1B
ADRA2BCREB1DBTHDAC9CD226CD14ITGA2FSHBMAPKAPK2
CD19IL1AABCG2CD4CDKN1AIFNA17IFNA21HBEGFCYP4A11
STAG1GH1FDPSPRKAR1AFCGR2AITGA4C3DGAT2
PSMB7CSF2FDFT1APOA4ACTG1PPP2CAADCY7ABCG1
SQSTM1IGLV11-55ABCG5APOA2PRKCANFE2XCL1FDPS
COL4A2EP300CYP19A1PNLIPFGF19P2RX1ROCK2FDFT1
CXCL12RPS27ALCTMAMLD1ERBB4ITGB1C5TGS1
COL4A1RNF123ABCG8APOA1ERBB3ITGAMMCF2LFABP6
CXCL11EGFSPTLC1SCG2ERBB2TEKGCGABCG5
COL4A4CD79ACYP2R1PRLMYH9ITGALHTR2BPLA2G10
PRKCAEGR1GOT1SDC2MAP2K4TFHTR2AACACB
COL5A2SELLGSRNR4A1MAPKAPK2TUBB3CALCACYP19A1
CALCRNFKBIATXNRD3SDC1PRKCEMAPK14OPRM1ACACA
CXCL10AAASDGAT1TGFB1PTENITGAXEDN1CYP2R1
COL4A3PRKAR2BALOX12STAT4PPP3R1SERPING1MCF2LSPTLC1
COL5A1GHRERCC2MYCIFNA21TUBB2AAPPABCG8
FGF19IRAK1ALOX15RBL1P2RX7ITGAVCCR7TXNRD3
ERBB4IRAK2AAASPARP1FGF23HGFAKT1FABP4
CXCL16PRKAR2ATSHBSTAT3LY86ITGB2CCR9FABP1
COL4A5PRKAR2BTAL1STAT1PPP2CAITGB3HRASDGAT1
ERBB3IFITM3ACAT1HIF1AFCGR3ACD36OPRK1ALOX12
ERBB2FCGR3ANNTLPLCD28APPPTHLHALOX15
PRKCHLGALS3TPI1RPLP0CYLDSH2B3OPN4SLC27A1
TIAM1DYNC1H1CD44HEY1HBEGFAKT1CHRM3APOF
COL6A1MICBALDH9A1GALFGF4SRCF2NEU1
EMBMICAHADHAPSMD13C3HRASRGS1CTGF
GPC5MAP3K8HADHBCD86FGF5MFN2CCR1PLA2G2A
PRKCEPIN1SMPD1APCSFGF2PDE5ACCR3APOE
LEPCAPZA1RAP1ASERPINE1MAPK10F11RCCR2ACADS
PTENUBA1SHDHEY2ADCY7SERPINF2CCR5SCAP

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
So are you trying to remove duplicates from each column as shown below in red? or do you want to remove all the duplicates in a row?



Code: 
[TABLE="class: cms_table"]
<TBODY>[TR]
[TD="class: cms_table_xl65, width: 64"]Signal Transduction[/TD]
[TD="class: cms_table_xl65, width: 64"]Immune System[/TD]
[TD="class: cms_table_xl65, width: 64"]Metabolism[/TD]
[TD="class: cms_table_xl65, width: 64"]Disease[/TD]
[TD="class: cms_table_xl65, width: 64"]Adaptive Immune System[/TD]
[TD="class: cms_table_xl65, width: 64"]Innate Immune System[/TD]
[TD="class: cms_table_xl65, width: 64"]Hemostasis[/TD]
[TD="class: cms_table_xl65, width: 64"]Signaling by GPCR[/TD]
[TD="class: cms_table_xl65, width: 64"]Metabolism of lipids and lipoproteins[/TD]
[/TR]
[TR]
[TD]ESR1[/TD]
[TD]PRKCG[/TD]
[TD]GLP1R[/TD]
[TD]PRKCG[/TD]
[TD]CD247[/TD]
[TD]PRKCG[/TD]
[TD][COLOR=#ff0000]PRKCG[/COLOR][/TD]
[TD][COLOR=#ff0000]PRKCG[/COLOR][/TD]
[TD]HADHA[/TD]
[/TR]
[TR]
[TD]PRKCG[/TD]
[TD]IL18[/TD]
[TD]NAMPT[/TD]
[TD]TGFBR1[/TD]
[TD]RAP1B[/TD]
[TD]CD247[/TD]
[TD]ATP2B2[/TD]
[TD]PRKCH[/TD]
[TD]HADHB[/TD]
[/TR]
[TR]
[TD]TGFBR1[/TD]
[TD]CD247[/TD]
[TD]OGDH[/TD]
[TD]TGFBR2[/TD]
[TD]RAP1A[/TD]
[TD]PRKCA[/TD]
[TD]PRKCH[/TD]
[TD]GLP1R[/TD]
[TD]GNPAT[/TD]
[/TR]
[TR]
[TD]TGFBR2[/TD]
[TD]NCAM1[/TD]
[TD]GNPAT[/TD]
[TD]NAMPT[/TD]
[TD]IRS2[/TD]
[TD]PYCARD[/TD]
[TD]ATP2B1[/TD]
[TD]OR13G1[/TD]
[TD]SMPD1[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Try the below code.

Code: 
Sub removeDuplicates()
Dim xrange As Range, cell As Range, column As Integer, cell2 As Range

column = 1
finalcolumn = Cells(1, Columns.Count).End(xlToLeft).column
Do
finalrow = Cells(Rows.Count, column).End(xlUp).row
Set xrange = Range(Cells(1, column), Cells(finalrow, column))
For Each cell In xrange
    If Application.CountIf(xrange, cell.Value) > 1 Then
        With cell
            cell.ClearContents
        End With
    End If
Next
column = column + 1
Loop Until column > finalcolumn
column = 1
finalcolumn = Cells(1, Columns.Count).End(xlToLeft).column
Do
finalrow = Cells(Rows.Count, column).End(xlUp).row
Set xrange = Range(Cells(1, column), Cells(finalrow, column))
For Each cell In xrange
    If cell.Value = "" Then
        cell.Delete
        For Each cell2 In xrange
            If cell2.Value = "" Then
                cell2.Delete
            End If
        Next
    End If
Next
column = column + 1
Loop Until column > finalcolumn
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Delete duplicate rows based on more than one column with excel macro
2
Replies
19
Views
932
kevin9999
K
F
  • Question
How to remove duplicates from one single cell
Replies
5
Views
424
FlashDota
F
F
  • Question
Remove duplicates in rows, not columns
Replies
2
Views
398
Joe4
Joe4
W
  • Question
Remove all duplicate rows if column A as duplicates but refer to column B
Replies
0
Views
392
Wisdomup
W
1
  • Question
Copying cells from multiple files in a folder
Replies
2
Views
402
171z
1

Forum statistics

Threads
1,216,514
Messages
6,131,105
Members
449,618
Latest member
lewismillar

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