Worksheet.Cells Property (Excel)

Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).

Syntax

expression .Cells

expression A variable that represents a Worksheet object.

Remarks

Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword. For more information, see the Item property and the examples for this topic.

Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet.

Example

This example sets the font size for cell C5 on Sheet1 to 14 points.

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14

This example clears the formula in cell one on Sheet1.

Worksheets("Sheet1").Cells(1).ClearContents

This example sets the font and font size for every cell on Sheet1 to 8-point Arial

With Worksheets("Sheet1").Cells.Font 
    .Name = "Arial" 
    .Size = 8 
End With

Sample code provided by: Tom Urtis, Atlas Programming Management | About the Contributor

This example toggles a sort between ascending and descending order when you double-click any cell in the data range. The data is sorted based on the column of the cell that is double-clicked.

Option Explicit
Public blnToggle As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim LastColumn As Long, keyColumn As Long, LastRow As Long
    Dim SortRange As Range
    LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    keyColumn = Target.Column
    
    If keyColumn <= LastColumn Then
    
        Application.ScreenUpdating = False
        Cancel = True
        LastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row
        Set SortRange = Target.CurrentRegion
        
        blnToggle = Not blnToggle
        If blnToggle = True Then
            SortRange.Sort Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes
        Else
            SortRange.Sort Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlYes
        End If
    
        Set SortRange = Nothing
        Application.ScreenUpdating = True
        
    End If
End Sub

About the Contributor

MVP Tom Urtis 是 Atlas 程式設計管理的創辦人，這是一家在矽谷提供 Microsoft Office 和 Excel 商務解決方案的公司。Tom 擁有 25 多年營運管理和開發 Microsoft Office 應用程式的資歷，而且是《Holy Macro! It’s 2,500 Excel VBA Examples》的共同作者。

請參閱

概念

Worksheet Object

Worksheet Object Members