VBA – Tips & Tricks

VBA – Delete PivotTables

This code snippet shows how to loop through all pivot tables in all worksheets and delete them:

Sub DeletePivotTables()

    Dim Pt As PivotTable
    Dim Ws As Worksheet
    
'Loop through worksheets 
    For Each Ws In ActiveWorkbook.Worksheets
    Worksheets(Ws.Name).Select

'Loop through pivot tables         
    For Each Pt In Ws.PivotTables
           
'Delete pivot table
           Pt.PivotSelect "", xlDataAndLabel, True
    Selection.Delete Shift:=xlToLeft

            'Exit Sub 'Optional: Get out
        Next Pt
    Next Ws
End Sub  

Excel formula – Miscellaneous

I recently had to work with Excel formulas, and I encountered a small challenge. The problem was this: I had a table with variating height (the user could add and delete rows), that was likely to sometimes have 0 rows.Also, it wasn’t just the sum of the whole column I needed, as the SUM value should be at the bottom of the table, and a circular loop should be avoided. Therefore, the usual SUM() didn’t work.

I did not want to use VBA for this problem, so I turned to this solution:

Example: Column A: Names – Column B: Salary

1) Find the last cells that isn’t empty (in column A).
2) Make a SUM()-formula in column B that can take the row number from 1) as a parameter.

1) (Danish)

=(SUMPRODUKT(MAKS((A4:A65003<>"")*RÆKKE(A4:A65003)))) 

(English)

=(SUMPRODUCT(MAX((A4:A65003<>"")*ROW(A4:A65003))))

To make the solution understandable, I will enter the above formula in cell G1. Now, I have defined the last row(G1), and I just need a fixed first row, which I define as 2: I need to use the SUM() and the INDEKS() to sum the variable amount of rows:

(Danish)

=SUM(INDEKS(B:B;2):INDEKS(B:B;G1))

(English)

=SUM(INDEX(B:B;2):INDEX(B:B;G1))

Of course, put togther, it looks like this:

=SUM(INDEKS(B:B;2):INDEKS(B:B;(=(SUMPRODUKT(MAKS((A4:A65003<>"")*RÆKKE(A4:A65003)))) )))