What is Current Region Property in VBA

In this blog post, you will learn about CurrentRegion Property in Excel VBA.

CurrentRegion Property is useful when our dataset is continuously expanding. This property never asks to assign a whole range of datasets but a single cell reference from it.

What is the Current Region - Definition

The CurrentRegion is a range bounded by any combination of blank rows and black columns.

We will learn the current region property with the help of the below-given examples.

Suppose that we have a dataset, as shown in the image below:-

Now we will select or copy this image with the following VBA code.

Range (“A1:A10”).Select (for selecting the whole range from A1 to A10).

And for copying the above range:-

 Range(“A1:A10”).Copy (for copying the whole range from A1 to A10)

We can also select this dataset with the help of CurrentRegion property, see in the below image:-

You can see in the below image example that we took only single-cell reference for selecting the whole dataset.

These are the methods to copy and select the dataset for a single column dataset.

Now suppose that we expand our dataset up to (“A1:C10”), see in the below image:-

Now we will select the dataset again in the same way. 

For selecting and copying the above dataset, we will use the following VBA code:-

For Copying the dataset

Here you can see that we are using the range from "A1:C10".

But when we select or copy the above dataset with the CurrentRegion property, we do not mention the whole dataset range.

Current Region property is useful when we are working on expanding dataset.

By using the CurrentRegion property, we know that referring to a single cell can select or copy the whole range of the dataset.

But this property is not going to work if there is a blank row and column is existing within the dataset.

Now we will use the current region property to select or copy the below-given dataset.

In the below image, we use CurrentRregion property to copy the dataset, as you know that this property does not require to assign any range.

The whole dataset is divide into four parts because blank row and column.

We can see that only dataset which is beyond the blank row and the column is not selected or copied.

We can understand here the CurrentRegion property in the multiple examples as mentioned below.

The below dataset contains one blank row and a blank column, and with the help of this dataset, I want to explain that the definition of the CurrentRegion property, i.e how this property works/

Image -01

In the "Image-01" you can see that the CurrentRegion property not selecting or copying the dataset which is after the blank row and column.

 You can see in the below images, the CurrentRegion property is not doing anything with the dataset which is beyond the blank rows and blank columns.

Image - 02

Image - 03

Image - 04

So with the help of all these examples, I tried to explain the CurrentRegion property in a detailed way.

I hope you find this tutorial useful.

Please feel free to put your comments or suggestion into the below box

Thanks

Narendra