relatively new to VBA and first time posting a question. Most of the knowledge I have is from my coding days in university, 15+ years ago. The problem I'm trying to resolve is this.
I have an Excel Spreadsheet that provides data for some reports that I generate. This data, has several duplicate lines with possible redundant data. I'm trying to consolidate these lines into a single row and so far I have successfully done so. The problem occurs if there is only a single entry (one unique number), its not likely to occur, but it is a possibility.
What I've done is taken this code that I found, Quicker way to get all unique values of a column in VBA? and applied it to my code. I liked its simplicity. What I'm doing is populating an array with this code and if there is only one entry, it errors out. The error is "#13: Type mismatch"
Parts of the code is as follows:
Public RFCArray() As Variant
Public ColRFC As Long
Public FirstRow As Long
Public WsMacro2 As Worksheet
RFCArray = GetUniqueValues(WsMacro2, ColRFC) 'WsMacro2 is the variable with the spreadsheetname & ColRFC is the column reference for the unique values
Function GetUniqueValues(Ws As Worksheet, col As Long) As Range
With Ws
.Columns(col).RemoveDuplicates Columns:=1, Header:=xlNo
FirstRow = 2
If IsEmpty(.Cells(1, col)) Then FirstRow = .Cells(1, col).End(xlDown).Row
Set GetUniqueValues = Range(.Cells(FirstRow, col), .Cells(.Rows.Count, col).End(xldown))
End With
End Function
I've tried various methods that I thought would resolve this issue. I've also asked fellow coworkers with no success. I thought I would put it out to this community for assistance. Thank you in advance.
