Currentregion attributes of VBA

What is the currentregion attribute?
You may often see the currentregion attribute in the program code. It is a very useful attribute, return the area of cells (commonly referred to as the current area) around the active cell, which consists of empty rows and empty columns, as shown in 1.
Figure 1
Specifically, the current area is the rectangular area where the active cell is located. Each row and column of the rectangular area contain at least one data, surrounded by empty rows and columns, figure 1 lists four of the four cases (see the blue shadow area ). Within the range of the current region, no matter which cell the active cell is, the current region of the active cell is the same region. In the example above, the B5: D7 region, the current region of the active cell B5 is B5: d7: When the active cell is C6, its current region is still B5: D7.
Using the currentregion attribute is equivalent to selecting the "edit -- locate" command in the Excel worksheet and clicking the "locate condition" button in the pop-up "locate" dialog box, select the "current region" Option Button in the "positioning condition" dialog box, or use the CTRL + Shift + * key combination.

Some basic applications of the currentregion attribute
The currentregion attribute can be used for many operations. The following uses the worksheet Example 2 to explain the application of this attribute.
Figure 2
(1) return the number of rows in the title row in the region where the specified (or active) cell is located.
Worksheets ("sheet1"). Range ("h2"). value = _
Worksheets ("sheet1"). Range ("A1"). currentregion. listheaderrows
After the code is run in the example worksheet, "1" is returned, that is, the row number of the title row in the area where cell A1 is located is 1.
(2) returns the number of rows in the region where the specified (or active) cell is located.
Worksheets ("sheet1"). Range ("H3"). value = _
Worksheets ("sheet1"). Range ("A1"). currentregion. Rows. Count
After the code is run in the example worksheet, "11" is returned, that is, the number of rows in the area where cell A1 is located is 11.
(3) return the number of columns in the region where the specified (or active) cell is located.
Worksheets ("sheet1"). Range ("H4"). value = _
Worksheets ("sheet1"). Range ("A1"). currentregion. Columns. Count
After the code is run in the example worksheet, "4" is returned, that is, the number of columns in the area where cell A1 is located is 4.
(4) return the number of cells in the region where the specified (or active) cell is located.
Worksheets ("sheet1"). Range ("H5"). value = _
Worksheets ("sheet1"). Range ("A1"). currentregion. cells. Count
After the code is run in the example worksheet, "44" is returned, that is, the number of cells in the area where cell A1 is located is 44.
(5) Select a data area other than the title row in the area where the specified (or active) cell is located.
Worksheets ("sheet1"). Range ("A1"). currentregion. Resize (_
Worksheets ("sheet1"). Range ("A1"). currentregion. Rows. Count-Worksheets ("sheet1 ")._
Range ("A1"). currentregion. listheaderrows, worksheets ("sheet1"). Range ("A1"). currentregion ._
Columns. Count). offset (1, 0). Select
After the code is run in the example worksheet, the data area in the area where cell A1 is located, that is, the area A2: D11, except the row with the title is selected.
The following code is combined into a complete example to demonstrate some usage of the currentregion attribute. The program code is as follows:
Sub testcurrentregion ()
Dim RNG as range, WS as Worksheet
Set Ws = activeworkbook. worksheets ("sheet1 ")
Set RNG = ws. Range ("A1"). currentregion
WS. Range ("G2") = "number of lines in the current region title"
WS. Range ("h2"). value = RNG. listheaderrows
WS. Range ("G3") = "number of rows in the current region"
WS. Range ("H3"). value = RNG. Rows. Count
WS. Range ("G4") = "Number of columns in the current region"
WS. Range ("H4"). value = RNG. Columns. Count
WS. Range ("G5"). value = "number of cells in the current region"
WS. Range ("H5"). value = RNG. cells. Count
WS. Columns ("G: G"). entirecolumn. autofit
Msgbox "select a region except the title line in the current region"
RNG. Resize (RNG. Rows. Count-RNG. listheaderrows, RNG. Columns. Count). offset (1, 0). Select
End sub
The running result is shown in Figure 3. For the sample document, see currentregion# .xls.
Figure 3
(6) copy data from the current region to another location
Sub copycurrentregion ()
Sheets ("sheet1"). Range ("A1"). currentregion. Copy sheets ("sheet2"). Range ("A1 ")
End sub
The preceding code copies the area of cell A1 in worksheet sheet1 to the area of cell A1 in worksheet sheet2.
(7) format the data in the current region
Sub formatcurrentregion ()
With activecell. currentregion
. Font. Bold = true
. Font. colorindex = 3
End
End sub
In the preceding code, the area of the active cell in the worksheet is bold and set to red.
(8) automatically apply the format in the current region
Sub testautoformatcurrentregion ()
Worksheets ("sheet1"). Range ("A1"). currentregion. AutoFormat
End sub
The above code automatically applies the default format in the area where cell A1 is located in the worksheet sheet1. Of course, you can set the format to be applied. The default value is xlrangeautoformatclassic1.

Examples
[Example 1] search for blank cells in the current area and fill in
For the worksheet shown in figure 4, use the data in the cells with data above the blank cell to fill the blank cell.
Figure 4
You can write the following code:
Sub fillblankcells ()
Worksheets ("sheet1"). Range ("A1"). currentregion ._
Specialcells (xlcelltypeblanks). formular1c1 = "= R [-1] C"
Worksheets ("sheet1"). Range ("A1"). currentregion. value = _
Worksheets ("sheet1"). Range ("A1"). currentregion. Value
End sub
After the code is run, the blank cells in the area where cell A1 is located in the worksheet are filled with corresponding data, as shown in Figure 5.
Figure 5

[Example 2] A simple sorting example
The Worksheet shown in 6 is now sorted by 3rd columns in descending order.
Figure 6
The Code is as follows:
Sub testsort ()
Dim RNG as range
Set RNG = worksheets ("sheet1"). cells (1, 1). currentregion
RNG. Sort key1: = RNG. cells (1, 3), Order1: = xldescending, header: = xlyes
End sub
After the code is run, the data in the worksheet is arranged in ascending order of 3rd columns, as shown in 7.
Figure 7

Summary
1. The basic syntax of the currentregion attribute is:
<Cell Object>. Currentregion
2. You can use the currentregion attribute to return the region where the specified cell or active cell is located, and then use other attributes to operate the data in the region.