Lesson 18: Excel VBA Methods

Besides having properties, Excel VBA objects also comprise methods. Methods are used to perform certain operations.

18.1 The ClearContents Method

ClearContents is a method of the range object. It clears the contents of a cell or a range of cells.

Example 18.1

You can write the following Excel VBA code to clear the contents of a range of cells in a spreadsheet:

Private Sub CommandButton1_Click() 
 Range("A1:A6").ClearContents 
End Sub

You can also write Excel VBA code to let the user select his or her own range of cells and clear the contents by using the InputBox function, as shown in Example 18.2

Example 18.2

Private Sub CommandButton1_Click()
 Dim, selectedRng As String 
 selectedRng = InputBox("Enter your range") 
 Range(selectedRng).ClearContents 
End Sub

In order to clear the contents of the entire worksheet, you can use the following code:

Sheet1.Cells.ClearContents

18.2 The ClearFormats Method

But if you only want to clear the formats of an entire worksheet, you can use the following syntax:

Sheet1.Cells.ClearFormats

18.3 The Select Method

This method selects a range of cells specified by the Range object. The syntax is:

Range(“A1:A5”).Select

Example 18.3

Private Sub CommandButton1_Click()
 Range("A1:A5").Select	 
End Sub

Example 18.4

This example allows the user to specifies the range of cells to be seleted.

 Private Sub CommandButton1_Click() 
  Dim selectedRng As String 
  selectedRng = InputBox("Enter your range")

  Range(selectedRng).Select 
 End Sub

18.4 The Clear Method

To deselect the selected range, we can use the Clear method.

 Range("CiRj:CmRn").Clear

Example 18.5

In this example, we insert two command buttons, the first one is to select the range and the second one is to deselect the selected range.

Private Sub CommandButton1_Click()
 Range("A1:A5").Select 
End Sub

Private Sub CommandButton2_Click()
 Range("A1:A5").Clear
End Sub

Instead of using the Clear method, you can also use the ClearContents method.

18.5 The Autofill Method

This method performs an autofill on the cells in the specified range with a series of items including numbers, days of week, months of year and more. The syntax is

Expression.AutoFill(Destination, Type)

Where Expression can be an object or a variable that returns and object. Destination means the required Range object of the cells to be filled. The destination must include the source range. Type means type of series, such as days of week, month of year and more. The AutoFill type constant is something like xlFillWeekdays, XlFillDays, XlFillMonths and more.

　

Example 18.6

Private Sub CommandButton1_Click() 
 Range(“A1”)=1 
 Range(“A2”)=2
 Range("A1:A2").AutoFill 
 Destination:=Range("A1:A10") 
End Sub

In this example, the source range is A1 to A2. When the user clicks on the command button, the program will first fill cell A1 with 1 and cell A2 will 2, and then automatically fills the Range A1 to A10 with a series of numbers from 1 to 10.

Example 18.7

Private Sub CommandButton1_Click()
 Cells(1, 1).Value = "monday" 
 Cells(2, 1).Value = "Tuesday"
 Range("A1:A2").AutoFill 
 Destination:=Range("A1:A10"), Type:=xlFillDays 
End Sub

Example 18.8

This example allows the user to select the range of cells to be automatically filled using the Autofill method. This can be achieved with the use of the InputBox. Since each time we want to autofill a new range, we need to clear the contents of the entire worksheet using the Sheet1.Cells.ClearContents statement.

Private Sub CommandButton1_Click() 
 Dim selectedRng As String 
 Sheet1.Cells.ClearContents 
 selectedRng = InputBox("Enter your range") 
  Range("A1") = 1 
  Range("A2") = 2 
 Range("A1:A2").AutoFill Destination:=Range(selectedRng) 
End Sub