Code Producing different results each time i run it

[termeric]

I'm having a hard time figuring this one out. I am building an exhibit where I display a list of brokers groups by the employee that they work with.

my source data looks something like this

Broker Contact
customer 1 employee 1
customer 2 employee 1
customer 3 employee 2
customer 4 employee 1
customer 5 employee 2
customer 6 employee 1
customer 7 employee 1
customer 8 employee 2
customer 9 employee 3
customer 10 employee 3


I'm taking the second column with employee name and making a list of unique values and using that to determine how many times to loop through the list so I get something that looks roughly like this. My intention is then to loop through a report I have and count a bunch of different things, but I haven't got there yet.

employee 1
customer 1
customer 2
customer 4
customer 6
customer 7
employee 2
customer 3
customer 5
customer 8
employee 3
customer 9
customer 10


I have a few different lists of employees based on the office that they work out of, and so the first thing I do in my code is check which office I am running this for and build the last based on that. when I run this for the first office, it only displayed the first 2 employees and ignores the 3rd employee. that is unless I run it a second time and then It counts all 3 people. I've tried stepping through the code using F8 to go line by line and it seems to work fine when I do that, but otherwise its a little unpredictable when it will work and when it wont. Any thoughts on what would be causing such a thing?

Sub SetUpTop_Table()

Dim Rep As String, Broker As String, BrokerLong As String
Dim inputRow As Integer, inputEnd As Integer                                                    'Input Sheet Start and End
Dim LegRepEnd As Integer, LegBrokerEnd As Integer, ListBrokerEnd As Integer, Row As Integer
Dim i As Long, z As Long, k As Integer, l As Integer, m As Integer
Dim CFOSelect As String, ColEnd As Integer                                                      'positional
Dim LegBrokerName As String, InpBrokerName As String, TransBrokerName As String                 'Filter Variables



Sheets("Top Broker").Range("A3:Z500").EntireRow.Delete
inputEnd = Sheets("NBInput").UsedRange.rows.Count

Row = 3
LegRepEnd = GetCountA("Top Broker Legend", 4)
LegBrokerEnd = GetCountA("Top Broker Legend", 3)

CFOSelect = Sheets("Top Broker").Range("A1").Value
ColEnd = Sheets("Top Broker Input").UsedRange.Columns.Count
 
'This find the column where th einformation is located on Top Broker Input
'Row is incremented at diffrrent phases of the loop to keep track of where the output should be

For i = 2 To ColEnd
    If Sheets("Top Broker Input").Cells(1, i).Value = CFOSelect Then
        z = i + 1
        ListBrokerEnd = GetCountA("Top Broker Input", i)
        
        Sheets("Top Broker Legend").Columns(4).EntireColumn.Delete
        If ListBrokerEnd > 1 Then
            Sheets("Top Broker Input").Range(Sheets("Top Broker Input").Cells(1, z), Sheets("Top Broker Input").Cells(ListBrokerEnd, z)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Top Broker Legend").Range("D1"), Unique:=True
        Else
            Sheets("Top Broker Legend").Range("D1").Value = "No Top Brokers"
        End If
        
        
        'This Loops through the individul Reps on Top Broker Legend Column D, compares to the
        For j = 2 To LegRepEnd
            Rep = Sheets("Top Broker Legend").Range("D" & j).Value
            Sheets("Top Broker").Range("A" & Row).Value = Rep
            Sheets("Top Broker").Range("A" & Row).Font.Bold = True
            Row = Row + 1
            
            'This loops through the Top Broker fields on the Top Broker Input to create the Grid
            For k = 2 To ListBrokerEnd
                If Rep = Sheets("Top Broker Input").Cells(k, z).Value Then
                    Sheets("Top Broker").Range("A" & Row).Value = Sheets("Top Broker Input").Cells(k, i).Value
                    Sheets("Top Broker").Range("A" & Row).Font.Bold = False
                    Sheets("Top Broker").Range("A" & Row).InsertIndent 2
                        With Sheets("Top Broker").Range("B" & Row & ":R" & Row).Borders
                            .LineStyle = xlContinuous
                            .Color = vbBlack
                            .Weight = xlThin
                        End With
                    Row = Row + 1
                End If
                           
            
            Next k
        
        
        Next j

    End If
Next i
End Sub

Function GetCountA(SheetName As String, Col As Long) As Long
 Dim wks As Worksheet, rng As Range

Set wks = ThisWorkbook.Worksheets(SheetName)
Set rng = wks.Range(wks.Cells(1, Col), wks.Cells(wks.rows.Count, Col))

 GetCountA = Application.CountA(rng)
End Function

 

[footoo]

I haven't gone through your code but to convert your sample data and assuming row 1 is a header row :

Sub Rearrange()
Dim lr&, r&
lr = Cells(Rows.Count, "A").End(xlUp).Row
[A:B].Sort Key1:=[B1], Order1:=xlAscending, Header:=xlYes
For r = lr To 2 Step -1
    If Cells(r, "B") <> Cells(r - 1, "B") Then
        Rows(r).EntireRow.Insert
        Cells(r, "A") = Cells(r + 1, "B").Value
    End If
Next
[B:B].ClearContents
End Sub