VBA code for Pivot Table

Rowanhf12

New Member
Joined
Jan 22, 2016
Messages
27
I have the below VBA code for a pivot table
Code:
'RowLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductNo")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductText")
        .Orientation = xlRowField
        .Position = 2
    End With

This section of the code if for the rows of the Pivot Table.
What if The User had a dropdown menu and chose position 2 to be nothing(blank). As in they only wanted 1 field for the rows.
I know how to use the dim function so each of the ranges can change based on what's selected, but what if 'nothing/blank' is selected??

Any help?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Rowanhf12

New Member
Joined
Jan 22, 2016
Messages
27
I was thinking about using some If statements

Code:
Code:
Sub Rowan()


    Sheets("Pivot").Select
    
    Dim p1 As Excel.Range
    Set p1 = Range("H1")
    
    If p1 = 0 Then
    End If
    If p1 = 1 Then
        Sheets("Pivot").Select
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
        ActiveSheet.PivotTables("PivotTable1").ClearTable
        Range("A1").Select
        ActiveSheet.PivotTables("PivotTable1").Location = "Pivot!A1"
        Range("A1").Select
            With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductNo")
            .Orientation = xlRowField
            .Position = 1
    End If
    If p1 = 2 Then
        Sheets("Pivot").Select
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
        ActiveSheet.PivotTables("PivotTable1").ClearTable
        Range("A1").Select
        ActiveSheet.PivotTables("PivotTable1").Location = "Pivot!A1"
        Range("A1").Select
            With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductNo")
            .Orientation = xlRowField
            .Position = 1
            With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductText")
            .Orientation = xlRowField
            .Position = 2
    End If
End With
End Sub

So if the user only selected one output for the rows it would run the p1=1 section. if they choose 2 it runs the p1=2 section etc.
I'm getting a lot of 'End if without Block If' and 'Else without If' errors, depending on how I'm wording the code.
What Am I missing/ leaving out/ needing to delete??

Any help????
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,515
Members
414,074
Latest member
Matthew Kakde

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