How many times we need a loop to run on unique values in a column.

Report 1.

OrderDate Region Rep Item Units UnitCost Total
1/6/2016 East Jones Pencil 95 1.99 189.05
1/23/2016 Central Kivell Binder 50 19.99 999.50
2/9/2016 Central Jardine Pencil 36 4.99 179.64
2/26/2016 Central Gill Pen 27 19.99 539.73
3/15/2016 West Sorvino Pencil 56 2.99 167.44
4/1/2016 East Jones Binder 60 4.99 299.40
4/18/2016 Central Andrews Pencil 75 1.99 149.25
5/5/2016 Central Jardine Pencil 90 4.99 449.10
5/22/2016 West Thompson Pencil 32 1.99 63.68
6/8/2016 East Jones Binder 60 8.99 539.40
6/25/2016 Central Morgan Pencil 90 4.99 449.10
7/12/2016 East Howard Binder 29 1.99 57.71
7/29/2016 East Parent Binder 81 19.99 1,619.19

Lets say we want all the ‘Rep’ in an array . This is used to run a loop over entire file and do operations ‘Rep’ wise.

Let’s do declarations. and find the column number from Name first

‘———————————————————————–

Dim MyBook As Workbook
Dim MySheet As Worksheet

Set MySheet = ActiveWorkbook.Worksheets(ActiveSheet.Name)
Set MyBook = Application.Workbooks(ActiveWorkbook.Name)

MySheet.Select

‘ getColFromAddr and getAddress are UDF ( User Defined Functions)

Rep_Column = getColFromAddr(getAddress(MySheet, “Rep”))

‘Lets declare an array.

Dim Representatives() As String

‘ This make memory allocation

ReDim Representatives(0)

‘let’s make the second value in a sheet as the first unique value of an array. This is a time saver.

Representatives(0) = MySheet.Cells(2, Rep_Column)

‘ See how we use the Rep_Column ?

‘ Now let’s loop through the sheet and find unique elements.

For i = 3 To MySheet.UsedRange.Rows.Count

‘ lets check if the value at i is repeated.
found = 0
For j = 0 To UBound(Rep_Column()) ‘ Loop within a loop

If MySheet.Cells(i, Rep_Column) = Representatives(j) Then
found = 1
Exit For
End If

Next j

If found = 0 Then
‘ the value is not found
mybound = UBound(Representatives())
‘ if Preserve is not used then the new array will delete previous values
ReDim Preserve Representatives(mybound + 1)
Representatives(mybound + 1) = MySheet.Cells(i, Rep_Column)

End If
Next i

‘———————————————————————–

Bonus Tip: You can convert ‘Region’ and ‘Item’ same way to get their unique elements.

These snippets may not seem much at first but they are building blocks of much automation in VBA

 

Advertisements