An Excel 2010 VBA object contains method. A method usually performs certain operations. For example, ClearContents is a method that clears the contents of a cell or a range of cells.
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:
However, if you only want to clear the formats of an entire worksheet, you can use the following syntax:
In order to select a range of cells, you can use the Select method. This method selects a range of cells specified by the Range object. The syntax is as follows:
Private Sub CommandButton1_Click() Range(“A1:A5″).Select End Sub
To deselect the selected range, we can use the Clear method.
In this example, we insert two command buttons. The first button is used to select the range while the second button 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.
Another very useful method is the Autofill method. This method performs an autofill on the cells in the specified range with a series of items. The items may include numbers, days of week, months of year and more. The syntax is
*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 the type of series, such as days of the week, the month of year and more. The AutoFill type constant is something like xlFillWeekdays, XlFillDays, XlFillMonths and more.
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.
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
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