Various Clear Methods in Excel VBA code example

Cleaning

There are various clear methods in Excel which are developed to perform a specific cleaning to a cell or range in excel. Let’s understand each method with example:

Clear

Method clear complete object which includes cell contents, formulas and formatting if any.

Data source

Code example

Public Sub ClearExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to range object
    Set oRange = Selection
    
    'clear method
    oRange.Clear
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Output

ClearComments/ClearNotes

Clears all comments attached to given Range or selection. ClearNotes available in Office 365. Following data contains two comments:

Data source

Code example

Public Sub ClearCommentsExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to range object
    Set oRange = Selection
    
    'clear method
    oRange.ClearComments
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Output

ClearContents

Method will clear contents and formula if any of a Cell or Range or Selection in Excel. Below data source contains three cells two of them contains constant numbers and one contains formula:

Data source

Code example

Public Sub ClearContentsExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to range object
    Set oRange = Selection
    
    'clear method
    oRange.ClearContents
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Output

ClearFormats

Method clear formatting of selected range or cells in Excel except Font Color. Below data source depicting cells having formatting which will be cleared by VBA:

Code example

Public Sub ClearFormattingExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to range object
    Set oRange = Selection
    
    'clear method
    oRange.ClearFormats
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Output

ClearHyperlinks

Method clears all the hyperlinks available in selected cells or a range. Note: the ClearHyperlinks method removes hyper links but formatting remains:

Code example

Public Sub ClearHyperlinksExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to range object
    Set oRange = Selection
    
    'clear method
    oRange.ClearHyperlinks
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Output

ClearOutline

Method clears outline applied around cells. An outline can be placed to a cell or range using formatting dialog as shown below:

Data source

Code example

Public Sub ClearOutlinesExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to range object
    Set oRange = Selection
    
    'clear Outline
    oRange.ClearOutline
    
    'memory cleanup
    Set oRange = Nothing
End Sub

Output

Please leave your comments or queries under comment section also please do subscribe to our blogs to keep your self upto date.

Share on Facebook

Tweet