Automatically select data-containing cells in Excel

This VBA script will automatically select data-containing cells and exclude all empty cells in an Excel worksheet.

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

Have Your Say

Δ