Warning: Parameter 1 to wp_default_scripts() expected to be a reference, value given in /var/www/mariaevert.dk/public_html/vba/wp-includes/plugin.php on line 601

Warning: Parameter 1 to wp_default_styles() expected to be a reference, value given in /var/www/mariaevert.dk/public_html/vba/wp-includes/plugin.php on line 601
Archive Excel | VBA – Tips & Tricks

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

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

August 2019
M T W T F S S
« Feb
1234
567891011
12131415161718
19202122232425
262728293031