CODEWITHSUNDEEP
excelvba
Dunn
Dunn

Reputation: 515

Loop filters for all items in table column of excel

I'm trying to build a code that will filter a table by product type, of which there might be multiple items. I need the table to filter through each of the product types to build a hierarchy for each product family.

I'm thinking it should be something like:

Sub familyfilter()

 With ActiveSheet.ListObjects(1)

  For Each Item In .Columns(1)

    .AutoFilter.ShowAllData

    .Columns(1).autofilterrange.AutoFilter Field:=3, Criteria1:=Item

    '[insert sub call here for hierarchy]

  Next Item
 End With
End Sub

I won't know the values in the family column, so I can't set a list of values to cycle through.

So if anyone knows how to do this for all the items in the column, I'd appreciate it!!

Thanks

Upvotes: 0

Views: 709

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Ah, I see what the issue is. Since you are doing For each Item in .Columns(1), it's only going to look in the COLUMN altogether. A quick test is this:

Sub test()
Dim cel as Range
for each cel in columns(1)
    cel.select
next cel
end sub()

If you go through that line by line (using F8), you'll see it select the column once, loop then end.

The fix is to use a range, instead of a column:

Sub test()
Dim rng as range, cel as Range
Set rng = Range(Cells(1,1),cells([lastRow],1))

For each cel in rng
  ' Do whatever in the cell
Next cel

end if
end sub

For the last row, you have multiple options to get it. If your data is in one 'solid' table (i.e. no blanks in column 1 until you reach the end of your data), you can use this to get the last cell, lastCell = Cells(1,1).End(xldown).Row. Or, if you do have some gaps in Col. 1, lastCell = Cells(1048576,1).End(xlup).Row.

Edit: This should get you started - I'm not sure if the filter part will work in this loop, so you may need to put that outside of it, but let me know if the above helps.

Upvotes: 1

Related Questions