excel - Trying to create a range within a filtered range (trying to create a second filter)

Question

I'm trying to create a range within a filtered range. My code starts out by filtering the first set of data. Once this data is filtered (based on criteria in column G), I am trying to a create a range which would then be sorted based on date criteria to be sorted once more. I'm currently not sure how to re-create such parameters like "first row" and "last row" after the first set of filtering, but here's what I have so far. I cannot filter these at the same time, just for independent reasons.

ProjectSpreadsheet.Range("A1").AutoFilter Field:=7, Criteria1:=criterion

Dim ProjectCodes As Variant

For Each ProjectCodes In Array("teacup", "plate", "fork", "spoon", "table", "chair", "knife", "fork", "soda", "TEA")
    FilterRows ProjectCodes
        
                   

        With ProjectSpreadsheet
            
            'Loop until to the last row
            Dim LastRowProjectSpreadsheet As Long
            LRProjectSpreadsheet = .Range("A" & Rows.Count).End(xlUp).Row
        
            'Now we need to set a value to the relevant range. This is the range
            'before the data gets sorted
            Dim rRange As Range
            Set rRange = .Range("A2:O" & LRProjectSpreadsheet)
        
            'After sorting, we will end up with several data rows for
            'which we will need to sort from. Now that we have a filtered table,
            'we would then need to created a variable for our new filtered range
            Dim fltrdRng As Range
            Set fltrdRng = rRange.SpecialCells(xlCellTypeVisible)
            

Here's where I'm stuck at where it becomes harder for me to filter a second time. The code above works just fine.

            Dim aCell As Range
            
            Dim DateRange As Range
            Dim DateRangeLastRow As Long
            DateRangeLastRow = Range("L" & Rows.Count).End(xlUp).Row
            Set DateRange = fltrdRng.Range("L" & DateRangeLastRow).SpecialCells(xlCellTypeVisible)
            
            For Each aCell In DateRange
                Debug.Print aCell
            Next

' For Each aCell In fltrdRng ' Debug.Print aCell ' Next End With

Next ProjectCodes

question from:https://stackoverflow.com/questions/65660299/trying-to-create-a-range-within-a-filtered-range-trying-to-create-a-second-filt

Answer 1

Waitting for answers