VBA Clear and Format in Excel

VBA Clear and Format in Excel

What is Excel VBA?

VBA stands for Visual Basic for Applications. Excel VBA is Microsoft’s programming language for Excel and all the other Microsoft Office programs, like Word and PowerPoint. The Office suite programs all share a common programming language.

Why Use Excel VBA?

While users cannot directly manipulate the main Excel software through VBA, they can, however, master the art of making macros to optimize their time in Excel. There are two ways to make Excel macros.

The first method is to use the Macro Recorder. After activating the recorder, Excel will record all the steps a user makes and save it as a “process” known as a macro. When the user ends the recorder, this macro is saved and can be assigned to a button that will run the exact same process again when clicked. This method is relatively simple and requires no inherent knowledge of the VBA code. This method will work for simple processes.

Objects

Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES

Cells to clear: This VBA code clears all of the formats in range ("A1:B2"), therefore if you are using the exact VBA code the cells in which you want to clear the formatting need to be captured in this range.

Worksheet Name: Have a worksheet named Analysis..

ADJUSTABLE PARAMETERS

Cells to clear: Select the range of cells that you want to clear by changing the range ("A1:B2") in the VBA code to any range in the worksheet that contains the cells in which you want to remove the formatting.

Worksheet Selection: Select the worksheet that contains the cells in which you want to remove the formatting by changing the Analysis worksheet name, in the VBA code, to any worksheet in the workbook.

Explanation about how to clear formatting in cells

This tutorial explains and provides step by step instructions on how to clear all formatting in cells using the Excel and VBA methods.

Excel Methods: Using Excel you can clear the formatting in selected cells with the ribbon option. p>VBA Methods: Using VBA you can clear the formatting in a range that has been specified in the VBA code.

1) Clear Formats from a Range of Cells

This will clear formats for a range of cells.

Syntax

Expression.clearformat

Example

Sub Clearformat()
Range(" B1:B20").Clearformat
End Sub

2) Range.ClearComments method (Excel)

Clears all cell comments from the specified range

Syntax

expression.clearComments

Example

Sub clearcomment()
Range(" B1:B20").ClearComments
End Sub

3) Range ClearHyperlinks method(Excel)

We can use VBA clearhyperlinks to Clear only the Hyperlinks in a specific range, cells, or the entire worksheet. We can use the ClearHyperlinks method of Range Object.

Syntax

Expression.ClearHyperlink

Example

sub clearhyperlink()
Range(" B1:B20").ClearHyperlinks
End sub

Range Clear method(Excel)

Clears the entire object

Syntax

Expression.clear

Example

sub clearformat()
Range("B1:B20").Clearformat
Range("B1:B20").Clearcomments
Range(" B1:B20").ClearHyperlinks
Range("B1:B20").Clear
End sub

Please watching My Video is Below

Design by Vannak Vannaro