Excel VBA Print_UniqueTo_Column(), Sample Code + Download

In this article I will explain the function Print_UniqueTo_Column()Print_UniqueTo_Column() is a function I’ve written in VBA for Excel. You can download the code and file related to this article here.

Jump To:

As Input it receives the location of a column of data and an output location. It then copies the unique values in the input column to the output location. For the example take a look at column below:

Excel, VBA, Column of Data

The result after using the function Print_UniqueTo_Column() on it can be seen below:

Excel, VBA, Print Unique Values

The code for the function can be seen below:

'''
'Prints the unique values of the input column in the output columns
'row_Input: The starting row of the input column values
'column_Input: The column where the input values are located
'row_Output: The starting column to print the output values
'column_Output: The column to print the output values
'wrksheet_Input: The worksheet where the input values are located
'wrksheet_Output: The worksheet where the output values are located
'Return Value: The number of printed rows in the output sheet
Public Function Print_UniqueTo_Column(ByVal row_Input As Integer, _
ByVal column_Input As Integer, ByVal row_Output As Integer, ByVal _
column_Output As Integer, ByRef wrksheet_Input As Worksheet, _
ByRef wrksheet_Output As Worksheet) As Integer
   
Dim intCount As Integer
Dim ArrInput() As Variant
Dim i As Integer
Dim arrOutput() As Variant
Dim j As Integer
Dim counterOutput As Integer
Dim flagFound As Boolean

intCount = Get_Count(row_Input, column_Input, wrksheet_Input, True)
counterOutput = 1
If intCount <> 0 Then
    ReDim ArrInput(1 To intCount, 1 To 1)
    ReDim arrOutput(1 To intCount, 1 To 1)
    ArrInput = wrksheet_Input.Range(wrksheet_Input.Cells( _
    row_Input, column_Input), wrksheet_Input.Cells(row_Input _
    + intCount - 1, column_Input)).Value
    counterOutput = 1
   
    For i = 1 To intCount
        flagFound = False
        For j = 1 To counterOutput - 1
            If ArrInput(i, 1) = arrOutput(j, 1) Then
                 flagFound = True
            End If
        Next j
       
        If flagFound = False Then
        arrOutput(counterOutput, 1) = ArrInput(i, 1)
        counterOutput = counterOutput + 1
        End If
       
    Next i
    wrksheet_Output.Range(wrksheet_Output.Cells(row_Output, _
    column_Output), wrksheet_Output.Cells(row_Output + _
    intCount - 1, column_Output)) = ""
    wrksheet_Output.Range(wrksheet_Output.Cells(row_Output, _
    column_Output), wrksheet_Output.Cells(row_Output + _
    intCount - 1, column_Output)) = arrOutput
End If

Print_UniqueTo_Column = counterOutput - 1
End Function

'Retrieves the amount of data at a spcefic column
'intRow: Row to start searching for data
'intColumn: Column to start searching for data
'wrksheet: worksheet to start searching for data
Public Function Get_Count(ByVal intRow As Integer, ByVal _
intColumn As Integer, ByRef wrkSheet As Worksheet, _
ByVal flagCountRows As Boolean) As Integer
Dim i As Integer
Dim flag As Boolean

i = 1
flag = True
While flag = True
    If flagCountRows = True Then
        If wrkSheet.Cells(i + intRow - 1, intColumn) <> "" Then
        i = i + 1
        Else
        flag = False
        End If
    Else
        If wrkSheet.Cells(intRow, intColumn + i - 1) <> "" Then
        i = i + 1
        Else
        flag = False
        End If
    End If
Wend
Get_Count = i - 1
End Function

Input Parameters:

row_Input: The first row index of the first cell in the input data.

column_Input: The column index of the input data.

row_Output: The row index of the location to start printing the output data.

column_Output: The column index of the location to print the out data.

wrksheet_Input: Reference to the sheet where the input data is located.

wrksheet_Output: Reference to the sheet where the output data is to be printed.

Example:

For the case described above, the input column starts with the cell (4, 2). Therefore 4 and 2 will be passed as the row_Input, and column_Input parameters respectively. The output column was printed starting at the cell (4, 3) therefore 4 and 3 will be passed as the row_Output and column_Output parameters respectively. The input and output columns are both assumed to be on sheet1 therefore Sheet1 will be passed as the wrksheet_Input and wrksheet_Output parameters:

Sub Example()
Call Print_UniqueTo_Column(4, 2, 4, 3, Sheet1, Sheet1)
End Sub

You can download the code and file related to this article here.

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *