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

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

M | T | W | T | F | S | S |
---|---|---|---|---|---|---|

1 | ||||||

2 | 3 | 4 | 5 | 6 | 7 | 8 |

9 | 10 | 11 | 12 | 13 | 14 | 15 |

16 | 17 | 18 | 19 | 20 | 21 | 22 |

23 | 24 | 25 | 26 | 27 | 28 | 29 |

30 | 31 |

#### Recent Posts

- VBA – Import CSV file
- VBA – Get name of file without extension
- UserForm Listbox – Populate list and extract selected items
- VBA – Retrieve Last Row From More Than One Column
- VBA – Check Extension of File
- VBA – Delete PivotTables
- VBA – Add New WorkSheet After The Last Worksheet
- VBA – Toggle Between Open Excel Files
- VBA – Looping through all files in a folder
- VBA – Create and add items to dynamic arrays
- VBA – Loop through arrays
- Excel formula – Miscellaneous
- VBA – Delete all files in a folder
- VBA – Loop through sheets
- VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet