VBA Columns Property

VBA Columns Property

By using Columns property, you may:

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:

VBA Columns

As mentioned earlier, by using the 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 the “C” column in our Excel sheet:

Code:

Sub Column_ex()

'Hiding col C

Columns("C").Hidden = True

End Sub

Output:

VBA Columns Hide

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:

VBA Columns Delete

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:

VBA Columns Insert

Inserting three columns by a single statement:

Sub Column_ex()
'Inserting 3 columns

Columns("B:D").Insert

End Sub

Result:

VBA Columns Insert 3

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:

VBA Columns Interior

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:

Columns Interior Multi

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:

Columns Borders Multi

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!