Use Cells.Select to select every cell on the active sheet, the same as clicking the corner box above row 1:
Cells.SelectA 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 WithUsedRange 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.SelectVBA to Select All the Cells
- First, type the CELLS property to refer to all the cells in the worksheet.
- After that, enter a (.) dot.
- At this point, you’ll have a list of methods and properties.
- From that 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.
- 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 the type 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.SelectNow 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.SelectRelated Tutorials
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells
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.