Nicholas A. Drouillard

Nicholas Drouillard

Dynamic Range for Excel Tables

Range Options

There are two main options when naming a range in Excel; [(1) Named Range (2) Data Tables]. It is common for Excel users to prefer named ranges as the OFFSET() function can be used to define a dynamic range.

However, a data table behaves much like a traditional database table. These are easily loaded to the data model and used in SQL and Power Queries. Unfortunately, using OFFSET() to create a dynamic range is not an option with a data table.

Best of Both Worlds

Pictured below is a basic subroutine that I developed to allow dynamic ranges in my data tables. Adding this feature makes logical sense and can simplify query writing in Excel.

Excel Power Query
VBA Dynamic Table: Code Sample