Accessing the Present Area is without doubt one of the most beneficial VBA methods in Excel. Not solely can it choose a knowledge set or desk, it allows you to entry particular person cells, rows and columns inside the area; making it very helpful for brand new Excel builders:
- Search a whole desk
- Choose a number of columns to make use of in a chart
- Outline rows and cells for highlighting
An Instance Of Choosing The Present Area
The traditional syntax for choosing the currentRegion is as follows:
Vary(cellAddress).currentRegion.choose
Whereas it is smart to make use of the primary cell of the desk as the bottom for the area, any cell reference inside the area can be utilized. Let’s take a look at an instance of how you should use the present area in actual life.
For instance we have to search a whole desk beginning at “A1” for an outlined textual content string:
myText="textual content to seek for"
myRange="a1"
vary(myRange).currentRegion.choosefor every c in choice
if instr(c,myText) then
' match discovered
Finish if
subsequent
Accessing Cells, Rows And Columns Inside The Present Area
As a result of a area covers a number of rows and columns there are a number of completely different methods to entry objects inside the area:
In a 2 row by four column desk (2 x four) you would possibly wish to know the deal with of the final cell within the desk, so you’ll be able to add information to the following row. This code identifies the following cell deal with for information enter by discovering the ultimate row and transferring to the following cell.
Vary(myRange).currentRegion.choose
lastRow=choice.rows.depend
cellAddress=choice.rows(lastRow).cells(1).deal with
vary(cellAddress).offset(1,zero).activate
Equally, if we needed to format every row, we might have to know what number of columns had been included within the area:
cols=choice.columns.depend
And should you had three columns and needed to return the 2nd column to incorporate as a collection vary in a chart you might use this line of code:
myRange=choice.columns(2).deal with
In a three x four desk, this could return a price within the kind $B$1:$B$four which coincidentally is the format to incorporate the column in a chart.
It may be helpful to consider the Present Area as a group, and the cells, rows and columns are objects within the assortment having their very own properties. On this manner when you could entry the objects inside the area you are able to do so intuitively, slightly than counting on information of particular properties and strategies.
Abstract
The Present Area is one essentially the most helpful instruments for each new and skilled VBA builders. understanding of its strategies and properties is important for efficient and productive Excel purposes.