Loop through list and filter on data set

[Oasiskris]

Hi there

I have a data set within that data set i have a list of unique values that i want to filter on, these can be different every time.

I have written the below, which takes that column and makes it a unique list of values, i now want to filter on the data set going through each value in the list.

The unique values are in Column AQ, and i want to filter on the column AN. Can you help?

Sub RunSplitReport()

Application.ScreenUpdating = True

Application.DisplayAlerts = True



Call GetUniqueValues

Call LoopEachName





Application.ScreenUpdating = True

Application.DisplayAlerts = True



End Sub

Sub GetUniqueValues()



Sheets("Data").Select



Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

Range("AN1:AN" & LR).SpecialCells(xlCellTypeVisible).Copy



Range("AQ1").PasteSpecial Paste:=xlPasteValues



LR = Range("A" & Rows.Count).End(xlUp).Row

ActiveSheet.Range("AQ1:AQ" & LR).SpecialCells(xlCellTypeVisible).RemoveDuplicates Columns:=1, Header:=xlYes



End Sub



Sub LoopEachName()



Dim aNames As Variant, Itm As Variant



With Range("AQ1", Range("AN" & Rows.Count).End(xlUp))

.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

aNames = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value

For Each Itm In aNames

.AutoFilter Field:=1, Criteria1:=Itm



'Copy and paste data'



Next Itm

.AutoFilter

End With

End Sub

 

[JamesCanale]

You have office365. Look at the "UNIQUE" function and the "FILTER" function. I'm not too clear on what you want, but they may be able to help.

Show your data and the desired output, and there might be an easy enough VBA free answer.

 

[Peter_SSs]

Welcome to the MrExcel board!

From what I can understand of your post, I think you need to change Sub LoopEachName to something like this.

Sub LoopEachName()
  Dim aNames As Variant, Itm As Variant
  
  aNames = Range("AQ2", Range("AQ" & Rows.Count).End(xlUp)).Value2
  With Range("AN1", Range("AN" & Rows.Count).End(xlUp))
    For Each Itm In aNames
      .AutoFilter Field:=1, Criteria1:=Itm
      
      'Copy and paste data'
      
    Next Itm
    .AutoFilter
  End With
End Sub

BTW, when posting code, please indent your code and use code tags to post it. My signature block below has more on that.

 

[Oasiskris]

Sub LoopEachName() Dim aNames As Variant, Itm As Variant aNames = Range("AQ2", Range("AQ" & Rows.Count).End(xlUp)).Value2 With Range("AN1", Range("AN" & Rows.Count).End(xlUp)) For Each Itm In aNames .AutoFilter Field:=1, Criteria1:=Itm 'Copy and paste data' Next Itm .AutoFilter End With End Sub

Thank you that works perfectly.

Apologies on not putting indent on my code, lesson learnt for future

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[nkashyap3]

Thank you that works perfectly.

Apologies on not putting indent on my code, lesson learnt for future

your Code is fit my requirements, in
Sub LoopEachName()
Dim aNames As Variant, Itm As Variant
aNames = Range("AQ2", Range("AQ" & Rows.Count).End(xlUp)).Value2
With Range("F1", Range("F" & Rows.Count).End(xlUp))
For Each Itm In aNames
.AutoFilter Field:=1, Criteria1:=Itm 'Copy and paste data'
Next Itm
.AutoFilter
End With
End Sub

only difference is my filter column is F.

code come in
For Each Itm In aNames

getting error ( Run-time error 13)
Type Mismatch


can you help me in this to retifity this issue