Solved

vba code to keep unique data entries

Posted on 2014-03-11
4
229 Views
Last Modified: 2014-03-19
Hi Experts Excel 2007

Need vba code to do the following and apologies I cannot upload a sample file from my location.

Assume: table
Business Name.               Reference.
Abc.                                    Apple
Abc.                                    Orange
Dcf.                                    Bank
Edf.                                     Office
Edf.                                     Office

Based on the above table keep the unique values based on col d (reference) and delete the rest..in this case we have edf twice in col A and Office twice in col d so remove.

Need the macro to loop through 20000 rows of data..
Where there is a single line as with dcf keep in the table.
0
Comment
Question by:route217
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39920022
HI,

pls try

Sub Macro()
Set myRange = Range(Range("D2"), Range("d" & Rows.Count).End(xlUp))
For Idx = myRange.Rows.Count + 1 To 2 Step -1
    If Application.WorksheetFunction.CountIf(myRange, Range("D" & Idx).Value) > 1 Then
        Range("D" & Idx).EntireRow.Delete
    End If
Next
End Sub

Open in new window

Regards
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39920564
Have you tried the Remove Duplicates function in the Data Ribbon? (Alt+A M)

It seems to me it could help you in this case.

Thomas
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39920573
I rgonzo's code works for you, I'd frame it with the following to speed up code execution significantly:

Application.screenupdating=false
'...
Application.screenupdating=true
0
 

Author Comment

by:route217
ID: 39925896
Excellent feedback
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question