VBA Columns Property
By using Columns property, you may:
- Hide/unhide columns in the Excel sheet.
- Delete specified column.
- Change the interior color of an entire column or in the range
- Border color changes
- And many other things
The examples below show using the Columns property in VBA.
An example to show/hide columns
We will be using the following sample sheet for a few of our examples:
As mentioned earlier, by using the Columns Hidden property, you may hide specified columns in the Excel sheet.
The example below hides the “C” column in our Excel sheet:
Code:
Sub Column_ex() 'Hiding col C Columns("C").Hidden = True End Sub
Output:
Un-hiding the hidden columns
Execute this code:
Sub Column_ex() 'Un-hiding col C Columns("C").Hidden = False End Sub
Result
The C column should be visible again.
Deleting specified columns example
By using the Delete method, you may remove one or more columns in the Sheet.
The example below deletes columns B and C from our sample sheet:
Code:
Sub Column_ex() 'Deleting Cols B,C Columns("B:C").Delete End Sub
Result:
Inserting one or more columns example
The example below inserts one column after the A column. For that, we specified “B” in the Columns.Insert property as follows:
Sub Column_ex() 'Inserting a column Columns("B").Insert End Sub
Result sheet:
Inserting three columns by a single statement:
Sub Column_ex() 'Inserting 3 columns Columns("B:D").Insert End Sub
Result:
An example of setting the Interior color of the column in Range
The following VBA program will set the column D interior color by using Range.Columns.ColorIndex property.
We used an empty sheet for demo purposes:
Sub Column_ex() Dim rng As Range Set rng = Range("A1:E10") 'Setting background ccolumn of D rng.Columns("D").Interior.ColorIndex = 35 'Selecting that range to show all cells rng.Select End Sub
Output:
Setting multiple columns interior color example
Similarly, you may set multiple columns’ background color by specifying in the Columns property.
See the code below for changing the color of the B, C, and D columns:
Sub Column_ex() Dim rng As Range Set rng = Range("A1:E10") 'Setting background of column of B,C and D rng.Columns("B:D").Interior.ColorIndex = 40 'Selecting that range to show all cells rng.Select End Sub
Output:
An example of setting multiple columns border-color
The example below sets the border color of columns C, D, and F by Range.Borders.ColorIndex property.
Code:
Sub Column_ex() Dim rng As Range Set rng = Range("A1:E10") 'Setting Borders color of column C,D and E rng.Columns("C:E").Borders.ColorIndex = 7 End Sub
Output: