Last instance of a unique value in a filtered range

[Rolandbows]

I have a macro which is supposed to do the following:

• Extract a list of unique teacher names from an excel table
• Create a copy of the initial page for each unique entry
  • Rename the copy and filter it for that unique name
  • Extract a list of unique Class period names and sort based on these names.
  • Find the last instance of each unique class period name in this filtered and sorted list
  • Darken the bottom border of that row.
• Repeat with next unique teacher name.

It was working, I thought, but now it seems to find the last instance in the unfiltered list instead of after its filtered. Not sure what I did to break it.

Here is the code: (I hope I posted this correctly. I'm a first time poster, long time reader.

Sub GenerateTPages()    Dim NameDic As Object
    Dim PeriodDic As Object
    Dim Name_Column As Range
    Dim TName
    Dim PName
    Dim tmp As String
    Dim Entry As Range
    Dim tmpSheet As Worksheet
    Dim Period_Column As Range
    Dim DarkLine As Range
    Dim where As Range
    Dim LastColumn As Long
        
        


    Set NameDic = CreateObject("scripting.dictionary")
    Set Name_Column = ThisWorkbook.Sheets("Teacher").Range("T_Results[Testing Instructor]")
    For Each Entry In Name_Column
        tmp = Trim(Entry.Value)
        If Len(tmp) > 1 Then NameDic(tmp) = NameDic(tmp) + 1
    Next


    For Each TName In NameDic.keys
        Debug.Print TName


        'Copy sheet
        Sheets("Teacher").Copy after:=Sheets("Teacher IA")
        
        'Rename sheet
        Set tmpSheet = ThisWorkbook.Sheets("Teacher (2)")
        tmpSheet.Range("E1").Value = TName         'enter value for teacher name
        Sheets("Teacher (2)").Name = TName
        
        'Sort by TName
        tmpSheet.ListObjects(1).Range.AutoFilter Field:=4, Criteria1:=TName
        
        'Sort by Ascending
        Set Period_Column = tmpSheet.ListObjects(1).ListColumns(5).DataBodyRange
        tmpSheet.ListObjects(1).Sort.SortFields.Add Key:=Period_Column, SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With tmpSheet.ListObjects(1).Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        'Add Thick Border
        Set PeriodDic = CreateObject("scripting.dictionary")
        Set Period_Column = Nothing
        Set Period_Column = tmpSheet.ListObjects(1).ListColumns(5).DataBodyRange
        LastColumn = tmpSheet.ListObjects(1).ListColumns.Count
        For Each Entry In Period_Column
            tmp = Trim(Entry.Value)
            If Len(tmp) > 1 Then PeriodDic(tmp) = PeriodDic(tmp) + 1
        Next
        For Each PName In PeriodDic.keys
            Debug.Print PName
            On Error Resume Next
            Set where = Period_Column.Find(what:=PName, after:=Period_Column(1), searchdirection:=xlPrevious)
            Set DarkLine = Range(Cells(where.Row, 1), Cells(where.Row, LastColumn))
            Debug.Print DarkLine.Address
            With DarkLine.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            Debug.Print where.Address


                        
        Next PName
        PeriodDic.RemoveAll
        Set PeriodDic = Nothing
        Set where = Nothing
        
    Next TName




End Sub

 

[AlphaFrog]

Check if the alike names are identical in each row e.g.; Bob in one row and bob in another including caps and spaces.

This will ignore text-case differences. And look at xlValues which are the visible filtered cells instead of xlFormulas which are all cells in the range filtered or not.

Set where = Period_Column.Find(what:=PName, [COLOR=#ff0000]LookIn:=xlValues, LookAt:= xlwhole, [/COLOR]after:=Period_Column(1), searchdirection:=xlPrevious, [COLOR=#FF0000]MatchCase:=False[/COLOR])

 

[Rolandbows]

Works perfectly. Thank you so much.