How to SELECT ALL the Cells in a Worksheet using a VBA Code

Table of Content Close
VBA to Select All the Cells
Notes
Sheet Must Be Activated

In VBA, there is a property called CELLS that you can use to select all the cells that you have in a worksheet.

Cells.Select

VBA to Select All the Cells

  1. First, type the CELLS property to refer to all the cells in the worksheet.
    1-type-the-cells-property
  2. After that, enter a (.) dot.
  3. At this point, you’ll have a list of methods and properties.
    2-enter-a-dot
  4. From that list select “Select” or type “Select”.
    3-from-list-select-select-or-type-select

Once you select the entire worksheet you can change the font, clear contents from it, or do other things.

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Notes

  • The CELLS property works just like the way you use the keyboard shortcut Control + A to select all the cells.
  • When you run this VBA code, it will select all the cells even if the sheet is protected and some of the cells are locked.
  • It will select cells that are hidden as well.

Sheet Must Be Activated

Now you need to understand one thing here when you select all the cells from a sheet that sheet needs to be activated. In short, you can’t select cells from a sheet that is not activated.

Let’s say you want to select all the cells from the “Sheet1”. If you use the tye below code, you’ll get an error. You need to activate the “Sheet1” first and then use the “Cells” property to select all the cells.

Worksheets("Sheet1").Activate
Cells.Select

Now when you run this it will first activate the “Sheet1” and then select all the cells. This thing gives you a little limitation that you can’t select the entire sheet if that sheet is not activated.

Here’s another thing that you can do: You can add a new sheet and then select all the cells.

Sheets.Add.Name = "mySheet"
Cells.Select