Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

VBA EXCEL PROBLEM

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
SBrighty
Asked:
SBrighty
1 Solution
 
pepeluiCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now