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.selectfor 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.
