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


Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

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 ...
Prevent Duplicate Values in Excel
Tutorial: I show you how to prevent duplicate values being entered into Excel using Data Validation...
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
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
Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)