Last instance of a unique value in a filtered range

Rolandbows

New Member
Joined
Nov 20, 2015
Messages
4
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.

Code:
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
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.

Code:
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

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 MrExcel.com.
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 "mrexcel.com".
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
Top