Hello

I am using a pivot table to get a list of unique values in a range of data (this was the best way I could think of). When I do this manually the rows appear straight away but when I do this as a macro it will not show the values unless I put something into the data section. Is there a way of making it show the rows if I do not use the data section?

This is part of the very messy code I have cobbled together so far. hopefully there is something I can just add to this to make it show the list.

Sub Setup()
Application.ScreenUpdating = False


Dim BenType As String
Dim rng As Range

Dim wks As Worksheet
Dim wbk As Workbook
Dim rnn As String
                    
Set wbk = ActiveWorkbook
Set wks = Worksheets("All Data")
                    
                    'Sets range ofdata to be used
                    Set rng = wks.Range("a1").CurrentRegion 'assuming that is where the top of block is
                    
                    'Valids the Selected Data
                    If rng.Rows.Count < 2 Then
                        MsgBox "Pivot on one row of data does not make sense"
                        Exit Sub
                    Else
                        'Sets the ranges of data and gives it a Name
                        rnn = "pvtsource"
                        rng.Name = rnn
                    End If
                    
                    
                    'Add a new sheet, as you can't put a chart of the current sheet as the
                    'pivottables datasource breaks over the web in IE.
'                    Sheets.Add
                    
                    
                    With ActiveSheet
                    
                        'Name the Sheet
'                           .Name = "Charts"
                            
                            
                        'Start a Pivot Table Wizard
                            .PivotTableWizard SourceType:=xlDatabase, SourceData:= _
                                rnn, TableDestination:=Worksheets("Allowances & Benefits").Range("B2"), TableName:="PivotTable1" 'ActiveSheet.Range("A65536").End(xlUp).Offset(5, 0), TableName:="PivotTable1"

    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        .ColumnGrand = False
        .RowGrand = False
    End With
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Benefit Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Benefit Code")
        .Orientation = xlRowField
        .Position = 2
        
        
    End With
End With
Thanks in advance for any help.

Regards

Jim