VBA: How to Select All Cells with Data


You can use the following methods in VBA to select all cells with data in a sheet:

Method 1: Select Grid of Cells with Data

Sub SelectCellsWithData()

Range("A1").CurrentRegion.Select

End Sub

This particular macro will select a grid of cells with data starting from cell A1 of the currently active sheet.

Method 2: Select Individual Cells with Data

Sub SelectCellsWithData()

Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Activate

End Sub

This particular macro will select all of the individual cells with data from Sheet1.

The following examples show how to use each method in practice with the following sheet called Sheet1 in Excel:

Example 1: Select Grid of Cells with Data

Suppose we would like to select the grid of cells that contains data in Sheet1.

We can create the following macro to do so:

Sub SelectCellsWithData()

Range("A1").CurrentRegion.Select

End Sub

When we run this macro, we can see that the entire grid of cells that contains data in Sheet1 has been selected:

Note that this method uses the CurrentRegion property to select the range of cells bounded by empty cells.

You can find the complete documentation for the CurrentRegion property here.

Example 2: Select Individual Cells with Data

Suppose we would like to only select all of the individual cells that contain data in Sheet1.

We can create the following macro to do so:

Sub SelectCellsWithData()

Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Activate

End Sub

When we run this macro, we can see that each of the individual cells in Sheet1 that contain data are selected:

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Set Cell Value in Another Sheet
VBA: How to Select Range from Active Cell
VBA: How to Delete Rows Based on Cell Value

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *