Is a PivotTable with VBA the best way to dynamically add/remove columns and rows ?

[MasterBash]

Hello,

I am almost done with my project. I sincerely appreciate the help, so does my team. They really like the improvements.

I believe this will be my last question involving this project.

Now... I am looking to dynamically extract data from a table. Currently, I did something manually, but I *think* simply using a PivotTable may be a better option ? I would like some thoughts. Please see the screenshot.
Why a PivotTable ? It doesn't show the rows and columns when the sum of the values = 0. However, the problem that I am facing is that... When that row or column does not equal 0 anymore, I have to add that specific data back under "Values" everytime.

The screenshot contains some explanations.

Thank you.

 

[Max1616]

If you'd like your pivot table to work, you need to set up your source table differently. If you set up your source table like this:

Then you can have your pivot table like this:

To filter out rows/columns with zero, then you can go to your pivot pane and filter by values > 0:

Filter both Ship by and Animal:

Now if you were to add a new row, or populate an existing row with a number, and refresh your pivot, the data will show up:

refreshed pivot:

 

[MasterBash]

Thank you ! Unfortunately, it is not possible for me to set up the table differently. However, maybe I can create another table like yours on a hidden sheet and try to automatically rearrange data from the first table on that new table for the pivot table to work.

I am not sure what is the easiest way to accomplish what I am trying to do. Maybe a dynamic table instead of a pivot table?

 

[MasterBash]

How can I easily set up a new table based on the initial table ? As I can't set up my source table differently. I think I will have to make the PivotTable based on the second table and not the source table.

 

[Max1616]

This macro will transform your source table into a table that can be pivoted:

Sub NewTable()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim lrow As Long
Dim lclm As Integer
Dim i As Long
Dim j As Integer
Dim lrow2 As Long

Set ws1 = Sheets("Sheet1") 'Rename "Sheet1" to whatever the sheet name that your source table lives on
Set ws2 = Sheets.Add

'Find the last row and column of your table
lrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row 'This assumes there is no data below the table
lclm = ws1.Cells(1, Columns.Count).End(xlToLeft).Column 'This assumes there is no data to the right of the table


ws2.Range("A1") = "Ship By"
ws2.Range("B1") = "Animal"
ws2.Range("C1") = "Amount"


For i = 2 To lrow
    For j = 2 To lclm
        If ws1.Cells(i, j) <> "" Then
        
            lrow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        
            ws2.Cells(lrow2 + 1, 1) = ws1.Cells(i, 1)
            ws2.Cells(lrow2 + 1, 2) = ws1.Cells(1, j)
            ws2.Cells(lrow2 + 1, 3) = ws1.Cells(i, j)
            
        End If
    Next j
Next i

End Sub

Note, this macro assumes there is no data below or to the right of the source table. Also, you need to rename the variable in the macro for ws1 (change "Sheet1" to whatever the sheet name is of the source table)

 

[RoryA]

I'd suggest power query to unpivot the columns into rows so that your data is better laid out for a pivot table.

 

[MasterBash]

This macro will transform your source table into a table that can be pivoted:

Sub NewTable()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim lrow As Long
Dim lclm As Integer
Dim i As Long
Dim j As Integer
Dim lrow2 As Long

Set ws1 = Sheets("Sheet1") 'Rename "Sheet1" to whatever the sheet name that your source table lives on
Set ws2 = Sheets.Add

'Find the last row and column of your table
lrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row 'This assumes there is no data below the table
lclm = ws1.Cells(1, Columns.Count).End(xlToLeft).Column 'This assumes there is no data to the right of the table


ws2.Range("A1") = "Ship By"
ws2.Range("B1") = "Animal"
ws2.Range("C1") = "Amount"


For i = 2 To lrow
    For j = 2 To lclm
        If ws1.Cells(i, j) <> "" Then
       
            lrow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
       
            ws2.Cells(lrow2 + 1, 1) = ws1.Cells(i, 1)
            ws2.Cells(lrow2 + 1, 2) = ws1.Cells(1, j)
            ws2.Cells(lrow2 + 1, 3) = ws1.Cells(i, j)
           
        End If
    Next j
Next i

End Sub

Note, this macro assumes there is no data below or to the right of the source table. Also, you need to rename the variable in the macro for ws1 (change "Sheet1" to whatever the sheet name is of the source table)

Sorry for taking a long time to reply, I was on vacation.
Thank you ! Unfortunately, there is data below/to the right of the table.

I'd suggest power query to unpivot the columns into rows so that your data is better laid out for a pivot table.

Oh, great ! Thank you so much. I never played with power query before, but I was able to figure it out. The only downside is that we must refresh the query everytime we add information to the source table, then refresh the PivotTable, correct ? Is there a way to refresh both at the same time, like a macro or something ?

 

[MasterBash]

Some progress...

I'd suggest power query to unpivot the columns into rows so that your data is better laid out for a pivot table.

Sub RefreshQuery()
ThisWorkbook.Worksheets("sheetname").ListObjects("querytablename").QueryTable.Refresh BackgroundQuery:=False
End Sub

I was able to get the query to refresh with a macro. However, I am unable to get the PivotTable to refresh in the same macro.
I am going to assign the macro to a shape (button).

 

[MasterBash]

Sorry in advance for replying to my own thread, but when I am making progress on something, I like to share the information, in case somebody needs it in the future and to avoid looking for a solution if I already found one...

Very simple actually :

Sub RefreshQuery()
ThisWorkbook.Worksheets("SheetName").ListObjects("QueryBame").QueryTable.Refresh BackgroundQuery:=False
ThisWorkbook.Worksheets("SheetName").PivotTables("PivotTableName").RefreshTable
End Sub

BackgroundQuery:=False is important here, otherwise the PivotTable will not refresh with the new data (I am guessing it has to do with priority, so the PivotTable may be updating before the query or something).

Thank you very much for your help Max and Rory !