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:
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.
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.
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