How To Use The VBA Current Region Method In Excel

Sonoma Goods For Life™ 2-pack Fret Embroidery Window Curtains
Give your home classy new style with this Regal Home Collections 2-pack Fret Embroidery curtain.

ScentWorx Juicy Honeydew Cucumber Body Lotion
The ultimate fragrance experience that delivers smooth, soft & nourished skin, ScentWorx Juicy Honeydew Cucumber Body Lotion will soothe and moisturize your skin with an amazing combination of Ripe Honeydew and Cantaloupe Melon blended with Fresh Crisp Cucumber. Our lightweight body lotion enrich...

Accessing the Current Region is one of the most valuable VBA techniques in Excel. Not only can it select a data set or table, it enables you to access individual cells, rows and columns within the region; making it very useful for new Excel developers:

  • Search an entire table
  • Select multiple columns to use in a chart
  • Define rows and cells for highlighting

An Example Of Selecting The Current Region

The normal syntax for selecting the currentRegion is as follows:


While it makes sense to use the first cell of the table as the base for the region, any cell reference within the region can be used. Let’s look at an example of how you can use the current region in real life.

Let’s say we need to search an entire table starting at “A1” for a defined text string:

myText="text to search for"

for each c in selection
if instr(c,myText) then
' match found
End if

Accessing Cells, Rows And Columns Within The Current Region

Because a region covers multiple rows and columns there are a few different techniques to access items within the region:

In a 2 row by 4 column table (2 x 4) you might want to know the address of the last cell in the table, so you can add data to the next row. This code identifies the next cell address for data input by finding the final row and moving to the next cell.


Similarly, if we wanted to format each row, we’d need to know how many columns were included in the region:


And if you had 3 columns and wanted to return the 2nd column to include as a series range in a chart you could use this line of code:


In a 3 x 4 table, this would return a value in the form $B$1:$B$4 which coincidentally is the format to include the column in a chart.

It might be useful to think of the Current Region as a collection, and the cells, rows and columns are items in the collection having their own properties. In this way when you need to access the items within the region you can do so intuitively, rather than relying on knowledge of specific properties and methods.


The Current Region is one the most useful tools for both new and experienced VBA developers. A good understanding of its methods and properties is essential for effective and productive Excel applications.

Simply Vera Vera Wang Two Tone 14k Gold 1/2 Carat T.W. Diamond Square Halo Ring
Adorned with round-cut diamonds arranged in a halo motif and along the split-shank band, this two-tone engagement ring abounds with sophisticated appeal. Convey your commitment in a timeless way when you present her with this Simply Vera Vera Wang square halo engagement ring.

Adult Kent 700c Front Runner Bike
Hit the ground running on this Adult Kent 700c Front Runner Bike. In black.

Jammies For Your Families® Star Wars The Mandalorian The Child aka Baby Yoda Pajama Collection
Calling all Baby Yoda fanatics to this festive Jammies For Your Families The Mandalorian pajamas collection!

Women's RED HOT by SPANX® Mid-Thigh Body Shaper SS3515
Comfort is key with this women's RED HOT by SPANX body shaper. The ultra-thin, non-compression fabric expands with your breath, so you look and feel fabulous.

Coconuts by Matisse Timber Women's High Heel Combat Boots
Elevate your ensemble with these Timber boots from Coconuts by Matisse.

Men's Tek Gear® Tricot Pants
Give your activewear a leg up with these sporty and comfortable men's workout pants from Tek Gear. These men's active pants feature a classic tricot finish for a look you can take to the gym or the streets. Not to mention, these gym pants for men deliver comfort wherever your day takes you.

You may also like...