30 Advanced Excel Tips for 2026

Excel Champs / VBA / How to SELECT ALL the Cells in a Worksheet using a VBA Code
By Puneet Gogia (Microsoft MVP)

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

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Use Cells.Select to select every cell on the active sheet, the same as clicking the corner box above row 1:

Cells.Select

A sheet has to be active before you can select on it. To select all the cells on another sheet, use Application.Goto, which switches to that sheet first: Application.Goto ThisWorkbook.Worksheets("Data").Cells. Calling Worksheets("Data").Cells.Select while another sheet is active stops with error 1004.

To select only the part of the sheet that holds data, use ActiveSheet.UsedRange.Select. For the block of data around the active cell, which is what Ctrl + A picks on its first press inside a table, use ActiveCell.CurrentRegion.Select.

Most code does not need to select anything. Act on the range directly, which is faster and works on any sheet without activating it:

With ThisWorkbook.Worksheets("Data").Cells
    .Font.Name = "Calibri"
    .Font.Size = 11
End With

UsedRange can be bigger than your data. It still counts cells that once held values or formatting, so it may include empty rows at the bottom. Delete the unused rows and columns and save the file to reset it.

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.
    type-the-cells-property
  2. After that, enter a (.) dot.
  3. At this point, you’ll have a list of methods and properties.
    enter-a-dot
  4. From that list, select “Select” or type “Select”.
    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.

Notes

  • The CELLS property works just like the way you use the keyboard shortcut Control + A to select all the cells (in a range of data, Ctrl + A first selects the data, and pressing it again selects the whole sheet).
  • 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.

The 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 “Sheet1”. If you use Worksheets(“Sheet1”).Cells.Select while another sheet is active, you’ll get run-time error 1004 (Select method of Range class failed). You need to activate “Sheet1” first and then use the “Cells” property to select all the cells, like in the code below.

Worksheets("Sheet1").Activate
Cells.Select

Now when you run this, it will first activate “Sheet1” and then select all the cells. This gives you a small limitation: 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

A new sheet becomes the active sheet as soon as you add it, so Cells.Select works on it straight away. Like the other short codes on this page, put these lines between Sub and End Sub in a module to run them.

Work with All Cells without Selecting Them

In most cases, you don’t need to select the cells at all. You can refer to all the cells of any sheet and change them directly, even when that sheet isn’t active. This is also faster:

Sub format_all_cells()
With Worksheets("Sheet1").Cells
.Font.Name = "Calibri"
.Font.Size = 11
End With
End Sub

Select Only the Cells with Data

If you only want the cells that hold your data, use one of these instead of Cells:

  • CurrentRegion: selects the block of data around a cell, just like pressing Ctrl + A once inside your data (code below this list).
  • UsedRange: selects everything from the first to the last used cell of the sheet, including cells that only have formatting.
Sub select_data_only()
Range("A1").CurrentRegion.Select
'or: ActiveSheet.UsedRange.Select
End Sub

Frequently Asked Questions

How do I select all the cells on every sheet?

Select all the sheets first with Worksheets.Select, and then use Cells.Select. The cells of all the selected sheets are grouped, so be careful: anything you type now goes into every sheet.

How do I deselect the cells after selecting all?

There’s no Deselect command in VBA. Select a single cell instead, like Range(“A1”).Select, so only that cell stays selected.

What is the difference between Cells and UsedRange?

Cells refers to every cell on the sheet, all 17 billion of them. UsedRange refers only to the area that has data or formatting, so it’s much smaller and faster to loop through.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel