## Excel

### 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

```
Sunday, March 20th, 2011 Comments Off on VBA – Delete PivotTables

### 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)))) )))
```
Wednesday, December 8th, 2010 Excel Comments Off on Excel formula – Miscellaneous

June 2024
M T W T F S S
12
3456789
10111213141516
17181920212223
24252627282930