Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 37 ยท Working with Excel

Excel VBA: Data, Tables and Charts

Sort, filter, remove duplicates, build ListObjects, PivotTables and charts with Excel VBA. Includes patterns for dictionaries and grouping totals.

Real Excel work is mostly data work: sort a list, filter rows, remove duplicates, total by category, and turn results into a chart. VBA can drive all of Excel's data tools, and it can also do things Excel cannot do alone, such as grouping with a dictionary.

This lesson covers sorting, auto-filter, duplicates, Excel Tables (ListObject), PivotTables and charts, plus the grouping pattern that appears in almost every report macro.

VBA cannot run on this site, so the listings are read-only. The JavaScript examples model the sorting and grouping logic, which is the part worth practicing.

Sorting

VB
With ws.Sort
    .SortFields.Clear
    .SortFields.Add Key:=ws.Range("C2:C500"), Order:=xlDescending
    .SortFields.Add Key:=ws.Range("A2:A500"), Order:=xlAscending
    .SetRange ws.Range("A1:D500")
    .Header = xlYes
    .Apply
End With

The Sort object supports several keys and keeps rows together. The older Range.Sort method also works for a single key, but the object form is clearer and avoids the "Header" guessing problem. Always set Header explicitly.

AutoFilter and advanced filtering

VB
ws.Range("A1:D500").AutoFilter Field:=3, Criteria1:=">100"       ' show rows where column 3 > 100
ws.Range("A1:D500").AutoFilter Field:=1, Criteria1:="Ada", Operator:=xlOr, Criteria2:="Bob"

' copy only the visible (filtered) rows
ws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Worksheets("Result").Range("A2")

ws.AutoFilterMode = False                     ' remove the filter

Calling AutoFilter with only the range toggles the drop-downs. With Field and criteria it applies a filter. Reset with ShowAllData or by turning AutoFilterMode off.

Removing duplicates

VB
ws.Range("A1:D500").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

Columns:=Array(1, 2) means two rows are duplicates when columns 1 and 2 both match. The first occurrence is kept.

Excel Tables (ListObject)

A table has a name, structured references and auto-expansion, which makes macros more robust.

VB
Dim lo As ListObject
Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range("A1").CurrentRegion, , xlYes)
lo.Name = "tblSales"
lo.TableStyle = "TableStyleMedium2"

lo.ListRows.Add                                ' add a row at the bottom
lo.ListRows(1).Range.Cells(1, 2).Value = 99    ' edit row 1, column 2
Debug.Print lo.ListColumns("Amount").DataBodyRange.Address

Using ListColumns("Amount").DataBodyRange means you do not need a last-row calculation: the table always knows where its data ends.

Grouping totals with a Dictionary

VB
Dim d As Object: Set d = CreateObject("Scripting.Dictionary")
Dim data As Variant: data = ws.Range("A2:B500").Value2
Dim i As Long, k As String
For i = 1 To UBound(data, 1)
    k = CStr(data(i, 1))
    d(k) = d(k) + data(i, 2)          ' missing key starts as Empty (= 0)
Next i

Dim key As Variant, outRow As Long: outRow = 2
For Each key In d.Keys
    Worksheets("Summary").Cells(outRow, 1).Value = key
    Worksheets("Summary").Cells(outRow, 2).Value = d(key)
    outRow = outRow + 1
Next key

Scripting.Dictionary is created through late binding (see late binding). Windows only; on Mac use a Collection instead.

PivotTables

VB
Dim pc As PivotCache, pt As PivotTable
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "tblSales")
Set pt = pc.CreatePivotTable(Worksheets("Pivot").Range("A3"), "ptSales")

With pt
    .PivotFields("Region").Orientation = xlRowField
    .AddDataField .PivotFields("Amount"), "Total Amount", xlSum
End With
pt.RefreshTable

A PivotTable is great for ad-hoc analysis; for fully controlled output, the dictionary pattern above gives more layout control.

Charts

VB
Dim co As ChartObject
Set co = Worksheets("Summary").ChartObjects.Add(Left:=200, Top:=10, Width:=360, Height:=220)
With co.Chart
    .SetSourceData Worksheets("Summary").Range("A1:B6")
    .ChartType = xlColumnClustered
    .HasTitle = True
    .ChartTitle.Text = "Sales by Region"
End With

You can also export a chart to an image with co.Chart.Export "C:\out\chart.png", handy for pasting into Word or e-mail.

Try it: sort and group like the macro does

JS Multi-key sort and group totals

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS RemoveDuplicates on key columns

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Forgetting Header on sort and filter, so Excel sorts the header row into the data.
  • Leaving filters on and then reading Range.Value, which still reads hidden rows.
  • Hard-coding row counts instead of using a ListObject or last-row function.
  • Adding a chart each run without deleting the previous one.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Columns:=Array(1, 2) in RemoveDuplicates mean?

    The array lists the key columns used to decide whether two rows are duplicates.
  2. 2. What is an advantage of a ListObject (Excel Table) for macros?

    ListColumns(...).DataBodyRange always covers the current data and grows as rows are added.
  3. 3. Which pattern totals amounts per category in one pass?

    A dictionary accumulates a running total per key in a single loop.
  4. 4. How do you remove an applied AutoFilter?

    Setting AutoFilterMode to False removes the filter drop-downs and shows all rows.

Summary

  • Sort with the Sort object and several SortFields; always set Header.
  • AutoFilter filters rows; SpecialCells(xlCellTypeVisible) reads the result.
  • RemoveDuplicates takes key columns; tables (ListObject) make ranges robust.
  • Use a dictionary for grouped totals and PivotTables or charts for presentation.

Next: Word Automation with VBA