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.
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.
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.
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:
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.
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: