How to Select Alternate Columns in Excel (or every Nth Column)

When dealing with databases in Excel, you might come across spreadsheets that have data organized in particular patterns.

For example, census data usually consists of alternating columns of actual data and standard errors. To get the gist of the data, you often need to select and separate out the columns containing the actual data.

In this Excel tutorial, I will show you two ways in which you can select alternate columns in Excel – a commonly known way, which is not that foolproof and a lesser-known way, which is actually quite foolproof, irrespective of the size of your data.

Towards the end of the article, we will also see how to select every nth column and how you can use the selected columns.

Table of Contents

Selecting Every Other Column in Excel using the Traditional Way

The traditional (and more commonly used) way of selecting alternate columns in Excel involves manually selecting each column, in the following way:

Select the first column by either selecting the column header or dragging down the column.
Press the CTRL key on the keyboard and select the next alternate column in the same way.
Repeat till you have selected all alternating columns.

While this is quite a straightforward way of selecting columns, it only helps when you have a smaller dataset, say one with about 15-20 columns.

For bigger datasets that involve hundreds of columns, this method is time-consuming and extremely error-prone.

One wrong selection and you’re done!

Selecting Every Other Column in Excel using VBScript

The more foolproof, but non-traditional way of selecting alternate columns in Excel involves the use of VBScript. We have put together a simple VBScript code for you that you can just copy and use as and when required.

Sub select_alt_columns()
Dim Rng As Range
Dim myCol As Range
Dim myUnion As Range
Set Rng = Selection
For i = Rng.Columns.Count To 1 Step -2
Set myCol = Rng.Columns(i)
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myCol)
Else
Set myUnion = myCol
End If
Next i
myUnion.Select
End Sub

Select all the cells of your dataset.

You can do this by selecting the first cell, and while pressing down on your SHIFT key, click the last cell (at the bottom right) of your dataset.

Here are the steps you need to follow:

From the Developer Menu Ribbon, select Visual Basic.
Once your VBA Editor window opens, click on Insert and then click Module. This will insert a new module for the current workbook
Double click on the module (in the Project Explorer) and paste the above code in the Module code window.
When you run this macro (Developer->Macros->select_alt_cells->Run) you will see all alternate columns of your sheet selected.

Once selected you can do whatever you want – such as hide these columns, delete these, highlight these, etc.

How does the VBA code work?

In the above code, we have selected every alternate column of the selection range and combined these individual column selections into a single selection using the Union function.

You might be wondering why it uses a Union function when you can just take individual columns and select them one by one.

Well, the reason is that when you try to do that, only the last column selected is considered. All other column selections before that are ignored. This is because the selected columns are not located adjacent to one another.

The Union method provides a great way to combine different cell selections located in different areas of a spreadsheet so that all these cells can be selected all at once.

Also read: How to Select Visible Cells Only in Excel?

How to Select Every nth Column in Excel

If you want to select every nth column (say every third column or every fourth column), you can use the same code as above, with a slight tweak.

All you need to do is change the number ‘-2’ in line 6 with the number of columns you want to skip.

So if you want to select every 3 rows, your code will be:

Sub select_third_columns()
Dim Rng As Range
Dim myCol As Range
Dim myUnion As Range
Set Rng = Selection
For i = Rng.Columns.Count To 1 Step -3
Set myCol = Rng.Columns(i)
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myCol)
Else
Set myUnion = myCol
End If
Next i
myUnion.Select
End Sub

That’s all! When you run the above code, you will find every third column of your data selected.

Also read: How to Select Every Other Row (Alternate Row) in Excel?

How to Highlight Alternate Columns

If you want to highlight every alternate column by changing the background color, you don’t need to select any column.

You only need to set the ‘style’ property of alternate columns to “Note”.

This can be done by just changing line 14 of the script from myUnion.Select to:

myUnion.Style = “Note”

So your code will now be:

Sub highlight_alt_columns()
Dim Rng As Range
Dim myCol As Range
Dim myUnion As Range
Set Rng = Selection
For i = Rng.Columns.Count To 1 Step -2
Set myCol = Rng.Columns(i)
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myCol)
Else
Set myUnion = myCol
End If
Next i
myUnion.Style = "Note"
End Sub

Another method you can use to highlight alternate columns (or every Nth column) is by using conditional formatting. You can use the same logic as shown in this tutorial.

How to Delete Alternate Columns

If you want to delete every alternate column, you can replace line 14 of the code to:

myUnion. Delete

So your code will now be:

Sub delete_alt_columns()
Dim Rng As Range
Dim myCol As Range
Dim myUnion As Range
Set Rng = Selection
For i = Rng.Columns.Count To 1 Step -2
Set myCol = Rng.Columns(i)
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myCol)
Else
Set myUnion = myCol
End If
Next i
myUnion.Delete
End Sub

Remember that when you delete columns using VBA, the change is irreversible. So if there is a chance that you may need the data in the future, it’s best to create a copy of the dataset.

Conclusion

In this Excel tutorial, we looked at two ways in which you can select every other column in Excel.

The first method was a manual method. This method, though simple, is not very effective when there are larger datasets involved.

The second method involves using a VBA script. You can use this script and tweak it depending upon your requirement.

We have shown how you can tweak it when you want to delete the selected cells or just highlight them too.

We hope this tutorial has been helpful to you.

Other excel tutorials you may find useful: