How To Use The VBA Current Region Method In Excel

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.