Skip to content

Automatically select data-containing cells in Excel

G29th January 2014

CNo Comments

TExcel, Visual Basic

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



No comments have yet been submitted. Be the first!

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.