Automatically select data-containing cells in Excel
To enable the creation of Pivot Tables in Excel, it’s important that only cells containing data are selected. Selecting blank cells in Excel sheets when creating Pivot tables leads to the creation of ‘blank’ columns or rows within the tables.
To highlight data-containing cells automatically, a VBA script can be written that identifies and highlights rows with data and excludes those without within the active worksheet. The columns range has been set to
A:ZZ to ensure that as much of the sheet is included as possible. The range could be narrowed if so desired.
Sub SelectDataCells() ActiveSheet.Columns("A:ZZ").SpecialCells(xlCellTypeConstants, 23).Select End Sub
If need-be, a specific worksheet can be selected instead. In this instance, the script will highlight cells in “My-Selected-Worksheet”:
Sub SelectDataCells() Worksheets("My-Selected-Worksheet").Columns("A:ZZ").SpecialCells(xlCellTypeConstants, 23).Select End Sub
This script will work even if there are gaps in the data (for example, if rows 1-15 and rows 19-22 contain data but rows 16-18 are blank).