What is Region Excel Add-in? code example

Region

A region is a range of data separated by blank cells or row or column headings. A common way to negate the advantages of having data in a region is to perform various account operations like showing sales figures, total sales etc. Following example shows two regions:

Data source

Range object provides CurrentRegion property that can be used for many operations that automatically expand the selection to include the entire current region, such as the AutoFormat method. This property cannot be used on a protected sheet else you will left with an error.

Code example

Public Sub SelectCurrentRegion()
	Worksheets("Sheet1").Activate
	ActiveCell.CurrentRegion.Select
End Sub

Another example, assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example.

Code example

Public Sub SelectCurrentRegionWithOffset()
	dim tbl as Range
	Set tbl = ActiveCell.CurrentRegion
	tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
End Sub

Next >> Dirty Cells in Excel

Share on Facebook

Tweet