Excel/VBA Columns Property: Explained with 6 Examples
By using Columns property, you may: Hide/unhide columns in the Excel sheet Delete specified columns Change the interior color of an entire column or in the range
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:
By using Columns Hidden property, you may hide specified columns in the Excel sheet.
Setting the Hidden value to True means hiding the column(s). False means unhide the columns.
The example below hides “C” column in our Excel sheet:
Code:
Sub Column_ex()
'Hiding col C
Columns("C").Hidden = True
End Sub
Output:
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 column 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 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 below 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 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:
