How to filter on 1 unique value at a time in VBA

[Bassie]

Hey,

I have around 7-10 unique values in column A and I want to filter by every single one of them and do some copy pasting etc. I just cant seem to figure out how to filter on the first unique value, do some stuff, then unfilter and filter on the next unique value.

Any help would be greatly appriciated.

 

[Trebor76]

Hi Bassie,

Try this:

Option Explicit
Sub Macro1()

    Dim wsSrc As Worksheet
    Dim i As Long
    Dim rngCell As Range
    Dim cln As New Collection
    
    Application.ScreenUpdating = False
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1") '<-Sheet with data. Change to suit if necessary.
    
    'Create an unique collection of entries in Col. A (starting at Row 2). Change to suit if necessary.
    For Each rngCell In wsSrc.Range("A2", wsSrc.Range("A" & Rows.Count).End(xlUp))
        If Len(rngCell) > 0 Then
            On Error Resume Next
                cln.Add CStr(rngCell), rngCell
            On Error GoTo 0
        End If
    Next rngCell
    
    'Loop through the unique collection filtering on each item
    For i = 1 To cln.Count
        wsSrc.Range("A1", wsSrc.Range("A" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=cln(i), Operator:=xlFilterValues
        '//Do stuff with the filtered data here//
    Next i

    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Peter_SSs]

Would something like this suffice?

Sub ProcessUniqueValues()
  Dim a As Variant
  Dim i As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = Evaluate("unique(" & .Address & ")")
    For i = 2 To UBound(a)
      .AutoFilter Field:=1, Criteria1:=a(i, 1)
      'Do your stuff here
    Next i
    .AutoFilter
  End With
End Sub