Return to VBA Code Examples

VBA Current Region

This tutorial will explain the how to use Current Region in VBA.

The CurrentRegion property in VBA is a way to select all the cells that are contained within a Range that you might want to work with. As long as the cells within the range are all consecutive or ‘touching’ each other, the CurrentRegion will select every cell in the region.

CurrentRegion Syntax

The syntax is very simply:

where you would specify the cell whose current region you wished to select and then use the CurrentRegion.Select property to select the Range of Cells.

Obtaining the Current Region

Consider the following worksheet.

vba current region database 1

the following sub procedure would select all the cells within the database

If we run the routine, all the cells in the CurrentRegion of cell E11 will be selected.

vba current region database 2

 

If we remove the contents of the adjoining cells, and re-run the routine, the following will be selected as the current region.

vba current region database 3

 

However if we remove even more data, we could end up with the example below for the Current Region of Cell E11.

vba current region database 4

 

Putting information ack into D13, we end up with the following:

vba current region database 5

The CurrentRegion therefore returns another range object defined by the smallest combination of occupied columns and rows that surround the Range you have supplied.

Counting the Rows and Columns in the Current Region

We can use CurrentRegion to count the rows and columns.

If we run the procedure, the following message box will appear.

vba current region database countrows

Clearing the Current Region

We can also use the current region property to clear the range of cells.

Assigning the Current Region to a Variable

We can also assign the entire Current Region to a range variable, and then use that range variable to manipulate the cells – be it formatting the cells, sorting the cells etc..

If we run the procedure above, we will end up with a worksheet as shown below!

vba current region format

Getting the Start and End Cells in the Current Region

With slightly more complicated code, we can get the first cell and last cell in a Current Region.

When we run the code above, the following message box will appear

vba current region database firstcell

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!