How to cycle through a filtered range using another auto filter on a different column?

[abhi.ko]

Hi,

I have a filter applied on column F based on a criteria (>1)

rng.AutoFilter Field:=6, Criteria1:=">1"

where rng is set for the data via VBA earlier.

Now from the filtered rows I want to apply another filter on Col E (5) and cycle through each of the unique visible values in Col E and perform some comparisons on the data and determine whether to keep it or delete those rows- but I don't know what values would be shown - that depends on the first filter - how do I accomplish this?

Here is a screenshot of the data with the first filter applied on Col F, now I would like to cycle through the 2 unique values (in this case) in Col E based on this filter:

If there is a more elegant solution than filter then I am open to that - I have tried Pivots and advanced filters but could not figure out a solution.

Thanks in advance and all help is appreciated.

 

[JoeMo]

Maybe I don't understand correctly what you want, but if there are rows you want to delete based on certain values in col E, wouldn't it be simpler to just filter col E on those values and then delete all visible rows?

 

[abhi.ko]

Thanks for the reply Jo.

It is a little bit more complicated - whether to delete or not would depend on Col E, Col F and whether the Original Term (oldest, these rows are sorted based on a date in Col A) and the Current Term (newest) are the same. Supplier term changes is what we are looking at - so if the terms changed from the original one and then changed back again and did this for maybe more than a few times but ultimately if they ended up with the same terms then effectively there is no change and that entry could be deleted. Hope that gives you some context.

Any suggestions on how to cycle through (using another filter) on this already filtered list - the values for the second filter would be unique values of Col E after the first filter is applied (in this case 14376760693CHICAIL and 143767INDIANAPOLIS IN).

 

[JoeMo]

Thanks for the reply Jo.

It is a little bit more complicated - whether to delete or not would depend on Col E, Col F and whether the Original Term (oldest, these rows are sorted based on a date in Col A) and the Current Term (newest) are the same. Supplier term changes is what we are looking at - so if the terms changed from the original one and then changed back again and did this for maybe more than a few times but ultimately if they ended up with the same terms then effectively there is no change and that entry could be deleted. Hope that gives you some context.

Any suggestions on how to cycle through (using another filter) on this already filtered list - the values for the second filter would be unique values of Col E after the first filter is applied (in this case 14376760693CHICAIL and 143767INDIANAPOLIS IN).

After you filter on col F, then filter on col E for 14376760693CHICAIL. Then if there are visible rows you would compare the original term to the current term and delete rows where these are identical. Repeat the process for 143767INDIANAPOLIS IN.

 

[abhi.ko]

Thanks again - but I'm coding for that using VBA, so the whole question is how do I apply a filter on Col F when I do not know the criteria. The data set changes every day when you run this macro.

Sub CashFlowReporting()

    Dim Dest, Source As Workbook
    Dim DestCell As Range
    Dim sh, ws, data As Worksheet
    Dim x, y, r, c, m, s As Integer
    Dim fname, sname, txt As String
    Dim starttime, endtime, dtDate As Date
    Dim ans As VbMsgBoxResult
    Dim rng, rng1 As Range
    Dim pt As PivotTable
    Dim pc As PivotCache
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    starttime = Now
    fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls;*.xlsx;*.xlsm), *.xls;*.xlsx;*.xlsm", Title:="Select the Term Changes Query Results file.")
    If fname = False Then Exit Sub
    
    ans = MsgBox("Is " & fname & "the Term Changes Query Results excel file?", vbYesNo)
    
    If ans = vbYes Then
        Workbooks.Open Filename:=fname
    Else
        MsgBox ("Please run the cash flow report genrator again and select the query results file.")
        Exit Sub
    End If
    
    Set Source = ActiveWorkbook
    Set sh = ActiveSheet
    
    sh.Range("E:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Value = "Number_Site"
    Range("F1").Value = "Count Num_Site"
    
    Range("E2").FormulaR1C1 = "=RC[-3]&RC[-1]"
    r = Range("A1").End(xlDown).Row
    Range("E2", Cells(r, "E")).FillDown
    Columns("E:F").AutoFit
    
    Set rng = Range("A1")
    Set rng = Range(rng, rng.End(xlToRight))
    Set rng = Range(rng, rng.End(xlDown))
    rng.Name = "Data"
    
    Range("A2", Range("A2").End(xlDown)).Name = "Date"
    Range("E2", Range("E2").End(xlDown)).Name = "Num_site"
    
    sh.Sort.SortFields.Clear
    sh.Sort.SortFields.Add Key:=Range("Num_site") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    sh.Sort.SortFields.Add Key:=Range("Date") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With sh.Sort
        .SetRange Range("Data")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Range("F2").Formula = "=countif($E$2:$E$1000,E2)"
    Range("F2", Cells(r, "F")).FillDown
    
    rng.AutoFilter field:=6, Criteria1:=">1"
    Set rng1 = rng.Rows.SpecialCells(xlCellTypeVisible)
    rng1.Select
    
' I would now want to filter based on field 5 but for each unique value within that field (cycle through it - in this case just 2 - could be more)


End Sub

 

[abhi.ko]

This got answered by a genius coder in another forum. Here is the solution (for the sake of anyone else who might run into the same issue), which was suggested and worked perfectly for the situation. Thanks to anyone who tried to solve this.

Dim rng As Range, ctv As Range, f As Long, vFLTR As Variant

vFLTR = ChrW(8203)


With ActiveSheet   'Set this worksheet reference properly!
    If .AutoFilterMode Then .AutoFilterMode = False
    Set rng = .Cells(1, 1).CurrentRegion


    With rng
        .AutoFilter Field:=6, Criteria1:=">1"
        If Application.Subtotal(103, .Columns(5)) > 1 Then
            With rng.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
                For Each ctv In .Columns(5).Cells.SpecialCells(xlCellTypeVisible)
                    If Not CBool(InStr(1, vFLTR, ChrW(8203) & ctv.Value & ChrW(8203), vbTextCompare)) Then
                        vFLTR = vFLTR & ctv.Value & ChrW(8203)
                    End If
                Next ctv
                vFLTR = Left(vFLTR, Len(vFLTR) - 1): vFLTR = Right(vFLTR, Len(vFLTR) - 1)
                vFLTR = Split(vFLTR, ChrW(8203))
            End With
            For f = LBound(vFLTR) To UBound(vFLTR)
                .AutoFilter Field:=5, Criteria1:=vFLTR(f)
                MsgBox "Pause and Proceed"
                .AutoFilter Field:=5
            Next f
        End If
        .AutoFilter
    End With
End With