CODEWITHSUNDEEP
excelvbaunique
lexi
lexi

Reputation: 17

Excel VBA Delete Blank Row After Creating Unique List From a Table Range

I modified a code from a question someone else asked on here (Make a new column without duplicates VBA?), which creates a unique list of values and copies it somewhere else.

In my case, I am extracting the values from Column F(Table A) and putting the unique values to Column G(Table B). The code for that works fine.

The problem I am having is that after the unique values are added to Column G, there is a blank row always added to the bottom, even if there are no blanks in the original.

How do I prevent the blank row from being added?

Sub Unique_Values()

Dim vaData As Variant
Dim colUnique As Collection
Dim aOutput() As Variant
Dim i As Long

'Put the data in an array, only checking 200 root causes for now.
vaData = Worksheets("Inputs1").Range("F2:F200").Value

'Create a new collection
Set colUnique = New Collection

'Loop through the data
For i = LBound(vaData, 1) To UBound(vaData, 1)
    'Collections can't have duplicate keys, so try to
    'add each item to the collection ignoring errors.
    'Only unique items will be added
    On Error Resume Next
        colUnique.Add vaData(i, 1), CStr(vaData(i, 1))
    On Error GoTo 0
Next i

'size an array to write out to the sheet
ReDim aOutput(1 To colUnique.Count, 1 To 1)

'Loop through the collection and fill the output array
For i = 1 To colUnique.Count
    aOutput(i, 1) = colUnique.Item(i)
Next i

'Write the unique values to column G
Worksheets("Inputs1").Range("G2").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput

End Sub

Thank you!

Upvotes: 0

Views: 216

Answers (1)

Antoine Driot
Antoine Driot

Reputation: 56

I think this part is weird :
I would put 1, not 2

UBound(aOutput, 2)

By the way, you are just using lists, not tables So, don't use UBound(aOutput, 2), just type UBound(aOutput)

For example, do :

'size an array to write out to the sheet
ReDim aOutput(1 To colUnique.Count)

'Loop through the collection and fill the output array
For i = 1 To colUnique.Count
    aOutput(i) = colUnique.Item(i)
Next i

Upvotes: 0

Related Questions