Last instance of a unique value in a filtered range


New Member
Nov 20, 2015
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

    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
        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
        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
        Set PeriodDic = Nothing
        Set where = Nothing
    Next TName

End Sub

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.


MrExcel MVP
Sep 2, 2009
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])
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

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
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 "".
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