CODEWITHSUNDEEP
excelvba
user7415328
user7415328

Reputation: 1083

Vba list unique values down a column from row x onwards?

I have two worksheets.

Worksheet 2:

Column C 
A
A
B
B
C
D
E
E
F
G

Worksheet 1:

Column G
A
B
C
D
E
F
G

As per the above, i am trying to create a list of unique values on sheet 1 column G from those in column C on sheet 2.

Here is my code so far:

Sub LIST()
   Dim Col As New Collection
    Dim itm
    Dim i As Long
    Dim CellVal As Variant
    Dim LastRow As Long

    '~~> Lets say looping through Row 1 to 22 For
    '~~> Range("A1:A22") as mentioned in your recent comment

    LastRow = ThisWorkbook.Worksheets("Data").Range("C" & Rows.Count).End(xlUp).Row  'Finds the last used row

    For i = 1 To LastRow
        CellVal = Sheets("Data").Range("C" & i).value
        On Error Resume Next
    Next i

    For Each itm In Columns(7)
    itm
    Next
    End Sub

I am brand new to vba, please can somsone show me how to get this to work properly?

P.S. i need this to list values in column G on sheet 1 from Row 16 onwards.

Upvotes: 0

Views: 501

Answers (2)

user3598756
user3598756

Reputation: 29421

The other way with Dictionary 

Sub LIST()
    Dim dict As Dictionary
    Dim cell As Range

    Set dict = New Dictionary 
    With Worksheets("Data")
        For Each cell In .Range("C1", .Cells(.Rows.Count, 1).End(xlUp)
            dict.Item(cell.Value) = 1
        Next
    End With 
    Worksheets("Sheet1").Range("G16").Value = Application,Transpose(dict.Keys)
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

As per Darren's comment:

Sub LIST()
    Dim r1 As Range, r2 As Range

    Set r1 = Sheets("Sheet1").Range("C:C")
    Set r2 = Sheets("Sheet2").Range("G1")

    r1.Copy r2

    r2.RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

If you wish to start below the first row, then replace "C:C" with something like "C7:C" & Rows.Count

Upvotes: 1

Related Questions