Automatically Updating Range References in VBA

Add to Favorites
Author: | Edits: don

How to make a range reference in VBA (Macros) that updates when new data is added to it or data is removed from it.

I show you how to do this for regular ranges and also for Tables in the worksheet in Excel.

Regular Range Dynamic Reference - VBA

Worksheets("Data").Range("A2", "A" & Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row).Select

Data is the worksheet that contains the range.

A2 is the start of the Range.

(Explained in more detail in the video above.)

Table Dynamic Reference - VBA

Worksheets("Data").ListObjects("tblColors").DataBodyRange.Select

Data is the worksheet that contains the Table.

tblColors is the name of the Table.

(Explained in more detail in the video above.)

 Additional Resources

Selecting and Activating Cells (microsoft resource)

How to Select Cells/Ranges (microsoft resource)


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Dynamic Named Range in Excel
Tutorial: How to create a named range that expands automatically when a new value is added to the r...
Absolute and Relative Cell References in Excel
Tutorial: In this tutorial I am going to cover the difference between Absolute and Relative Cell Ref...
3 Ways to Automatically Update Drop Down Lists in Excel - Data Validation
Tutorial: Ensure that your Data Validation Drop Down Lists or Menus are automatically updated with ...
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Prevent Duplicate Values in Excel
Tutorial: I show you how to prevent duplicate values being entered into Excel using Data Validation...
Automatically Updating Dependent Drop Down List Menus in Excel
Tutorial: Step-by-step instruction on how to make automatically updating dependent data validation ...
Tutorial Details
Downloadable Files: Excel File
Similar Content
Dynamic Named Range in Excel
Tutorial: How to create a named range that expands automatically when a new value is added to the r...
Absolute and Relative Cell References in Excel
Tutorial: In this tutorial I am going to cover the difference between Absolute and Relative Cell Ref...
3 Ways to Automatically Update Drop Down Lists in Excel - Data Validation
Tutorial: Ensure that your Data Validation Drop Down Lists or Menus are automatically updated with ...
Excel Forum