Home » Word Press » How To Use The VBA Current Region Method In Excel

How To Use The VBA Current Region Method In Excel

Accessing the Current Region is without doubt one of the most beneficial VBA ways in Excel. Not simplest can it make a selection an information set or desk, it allows you to get entry to person cells, rows and columns inside the area; Making it very helpful for brand spanking new Excel builders:

  • Search a complete desk
  • Select a couple of columns to make use of in a chart
  • Define rows and cells for highlighting

An Example Of Selecting The Current Region

The commonplace syntax for deciding on the presentRegion is as follows:


Range (cellAddress) .presentRegion.make a selection

While it is smart to make use of the primary cellular of the desk as the bottom for the area, any cellular reference inside the area can be utilized. Let's take a look at an instance of ways you’ll be able to use the present area in actual existence.

Let's say we wish to seek a complete desk beginning at "A1" for an outlined textual content string:


MyText = "text to search for"
MyRange = "a1"
Range (myRange) .presentRegion.make a selection

For every c in variety
If instr (c, myText) then
'Match discovered
End if
Next

Accessing Cells, Rows And Columns Within The Current Region

Because a area covers a couple of rows and columns there are a couple of other ways to get entry to pieces inside the area:

In a 2 row through four column desk (2 x four) you could wish to know the cope with of the closing cellular within the desk, so you’ll be able to upload information to the following row. This code identifies the following cellular cope with for information enter through discovering the general row and shifting to the following cellular.


Range (myRange) .presentRegion.make a selection
LastRow = variety.rows.rely
CellAddress = variety.rows (lastRow) .cells (1) .cope with
Range (cellAddress) .offset (1,zero) .turn on

Similarly, if we needed to layout every row, we'd wish to know the way many columns have been incorporated within the area:


Cols = variety.columns.rely

And if you happen to had three columns and sought after to go back the 2d column to incorporate as a sequence vary in a chart that you must use this line of code:


MyRange = variety.columns (2) .cope with

In a three x four desk, this could go back a worth within the shape $ B $ 1: $ B $ four which coincidentally is the layout to incorporate the column in a chart.

It is also helpful to think about the Current Region as a set, and the cells, rows and columns are pieces within the assortment having their very own houses. In this manner when you want to get entry to the pieces inside the area you’ll be able to achieve this intuitively, fairly than depending on wisdom of explicit houses and strategies.

Summary

The Current Region is without doubt one of the Most worthy gear for each new and skilled VBA builders. A just right figuring out of its strategies and houses is very important for efficient and productive Excel programs.

Check Also

Edufast Education | HTML5 Google Banner Ad (Ad Templates)

Edufast Education | HTML5 Google Banner Ad (Ad Templates)

Edufast Education | HTML5 Google Banner Ad (Ad Templates). Edufast Education | Animated Google Banner …