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

식 .Cells

식 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는 Microsoft Office 및 Excel 비즈니스 솔루션 관련 원스톱 서비스 업체인 실리콘 밸리 소재 Atlas Programming Management(영문일 수 있음)의 설립자입니다. Tom은 25년 이상 비즈니스 관리 업무를 맡고 Microsoft Office 응용 프로그램을 개발해 왔으며, "Holy Macro! It’s 2,500 Excel VBA Examples"를 공동 집필하기도 했습니다.

참고 항목

개념

Worksheet Object

Worksheet Object Members