Manipulate a table
- Mar. 7, 2021
 | Example files with this article: |
Manipulate a table
We all know that manipulating large amounts of data and input can be tricky at times. In that case I like to work with slicers, since it becomes very easy to filter out the desired record(s). With a little bit of VBA you don't even need to approach the table itself: a short procedure suffices to update the filtered record(s). To read up on slicers and how to use them, please refer to this page.
As an example, let's look at the companies and months and sales quantity:
On the left hand side, we see a table with data (3 columns for input, 1 containing a formula), on the right hand side I created 3 slicers. 1 slicer for every column that is there for data input or making selections.
Clicking the relevant option in the slicers will hide other options and less records will remain visible:
The VBA code is not difficult at all.
Dim i As Long
Dim r As Range
If Range("U2").Value > 1 Then
MsgBox Range("U2").Value & " rows still remain, please filter until only 1 row remains." & vbCrLf & "The macro is cancelled.", vbInformation
Exit Sub
End If
With ActiveSheet.ListObjects(1).ListColumns(4).DataBodyRange.Cells
For i = 1 To .CountLarge
Set r = .Cells(i)
If Not r.EntireRow.Hidden Then
r.Value = r.Value + 1
End If
Next
End With
The cell counts the number of visible rows. We make use of that cell in the code. After that we loop through the visible cells in column 4 of the table. In case the cell (row) is not hidden then we can increment the value of 1 to the cell value. We use the SUBTOTAL formula.
Using the plus sign near the top left hand corner, you can show the table and hide afterwards, although you typically won't need the table very often. It's also allowed to to store the table on a hidden/different sheet.
Using a template/idea like this is much recommended. Also, please take a look at the properties and settings for the slicers, such that you can adjust the formatting and behaviour of the slicers to have the biggest benefit for your spreadsheets.
