Manipulate a table

Example files with this article:
  • Manipulate a table
  • 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
        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.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links