Loop through list and filter on data set

Oasiskris

New Member
Joined
May 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
384
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
May 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,147,474
Messages
5,741,341
Members
423,656
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top