?
Solved

VBA EXCEL PROBLEM

Posted on 2003-02-21
1
Medium Priority
?
248 Views
Last Modified: 2010-04-17
Does anybody know how to check a Excel Worksheet column for values.  I want to return the values within a specified column to another column within the same worksheet.  the values column has duplicates,  I only want to return one copy of each value into my new values list...  Any ideas... I tries using Vlookup within Excel,  but had problems...

Thanks.

Steveeb.
0
Comment
Question by:SBrighty
[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
1 Comment
 
LVL 1

Accepted Solution

by:
pepelui earned 210 total points
ID: 7993402
Try this: copy data, paste a copy, sort copy, copy distinct values.



    Dim sourceRange, destCell, sourceCell As Range
    Dim lastValue As String
    Dim destOffset As Integer
   
    'Select range
    Set sourceRange = Range("A1:A20")
    'Copy range
    Application.CutCopyMode = False
    sourceRange.Copy
    'Select destination cell
    Range("B1").Select
    'Paste
    ActiveSheet.Paste
   
    'Select copied range
    Set sourceRange = Range("B1:B20")
    sourceRange.Select
   
    'Order data
    sourceRange.Sort key1:=Range("B1"), order1:=xlAscending, MatchCase:=False
   
    'Select destination range
    Set destCell = Range("C1")
   
   
    'Init compare value to something we won't find
    lastValue = "-1sdfrrdf23456"
    'Init offset to 0
    destOffset = 0
    For Each sourceCell In sourceRange
        If sourceCell.Value <> lastValue Then
            destCell.Offset(destOffset, 0).Value = sourceCell.Value
            destOffset = destOffset + 1
            lastValue = sourceCell.Value
        End If
    Next

    'Delete temporary data in sourceRange
    sourceRange.Delete

Of course, you have to change ranges to work in your worksheet.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Starting up a Project
Suggested Courses

770 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