Excel Macro to Select Every Other Column

Tonight's tip is on a macro that you can use to select every other, or every nth column in an Excel worksheet. The macro was posted to the site of Extend Office, the KuTools folks. As with any other macro simply press ALT + F11 to get into Visual Basic; right click on the worksheet name in the list to the left, and select Insert . . . Module. Paste in this VBA code:

Sub EveryOtherColumn() 'Updateby20140314 Dim rng As Range Dim InputRng As Range Dim OutRng As Range Dim xInterval As Integer xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8) xInterval = Application.InputBox("Enter column interval", xTitleId, Type:=1) For i = 1 To InputRng.Columns.Count Step xInterval + 1 Set rng = InputRng.Cells(1, i) If OutRng Is Nothing Then Set OutRng = rng Else Set OutRng = Application.Union(OutRng, rng) End If Next OutRng.EntireColumn.Select End Sub

When you run the macro it will prompt you to select a range of text. Then you are prompted to select a column interval. If you want to select every other column, just enter 1. If you select a 2, it will select the first column, skip the next two, select the third, skip the fourth and the fifth, and so forth - as shown in this example:

This macro can be useful then dealing with delimited text such as this sample separated with pilcrows and thorns.

þBEGDOCþ¶þENDDOCþ¶þFILENAMEþ¶þMODDATEþ¶þAUTHORþ¶þDOCTYPEþ þ0000001þ¶þ0000004þ¶þContractþ¶þ01/12/2013þ¶þJ. Smithþ¶þdocxþ þ0000005þ¶þ0000005þ¶þMemoþ¶þ02/03/2013þ¶þR. Jonesþ¶þdocxþ þ0000006þ¶þ0000073þ¶þTaxes_2013þ¶þ04/14/2013þ¶þH. Blockþ¶þxlsxþ þ0000074þ¶þ0000089þ¶þPolicyþ¶þ05/25/2013þ¶þA. Dobeyþ¶þpdfþ

We can easily use the Text to Column tool (on the Data tab . . . in the Data Tools section) in Excel to separate this data into separate columns. Delimited in Step 1; check off Other in Step 2 - in the box press ALT + 0254 on your number key pad. This enters the thorn þ as the delimiter. The trouble is that you can't select the pilcrow ¶ as a text qualifier. When you go ahead and press Finish, you get a worksheet in which the data for each field from the sample load file is in separate columns but these are then separated by columns with the pilcrows.

We can use the macro to just select the columns with the pilcrows and then right click and delete them.