Excel Macro to Select Every Other Column

Excel Macro to Select Every Other Column

November 25, 2016

 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.

 

 

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com