How To Use The VBA Current Region Method In Excel
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
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.
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!