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:


Range(cellAddress).currentRegion.select

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"
myRange="a1"
range(myRange).currentRegion.select

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

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.


Range(myRange).currentRegion.select
lastRow=selection.rows.count
cellAddress=selection.rows(lastRow).cells(1).address
range(cellAddress).offset(1,0).activate

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


cols=selection.columns.count

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:


myRange=selection.columns(2).address

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.

Summary

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!

You may also like...