How to Insert Specific Number of Columns at Fixed Intervals in Excel?
In your daily usage of Excel, you may run into a situation that you need to add more columns between the existing columns. You can, of course, insert the columns one by one using the Insert function. But when you are facing a large data and need to insert three columns after every second or nth column, the Insert function is apparently not effective. So how can you quickly finish the task? In this tutorial, we will talk about two easy methods to solve the problem.
We can use the below VBA code to insert a specific number of blank columns after every nth column. Please do as follows.
1. Press the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module and paste the following macro in the Module Window.
VBA code: Insert specific number of columns into data at fixed intervals
Sub InsertColumnsAtIntervals() 'Updateby Extendoffice Dim Rng As Range Dim xInterval As Integer Dim xColumns As Integer Dim xColumnsCount As Integer Dim xNum1 As Integer Dim xNum2 As Integer Dim WorkRng As Range Dim xWs As Worksheet xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) xColumnsCount = WorkRng.Columns.Count xInterval = Application.InputBox("Enter column interval. ", xTitleId, 1, Type:=1) xColumns = Application.InputBox("How many columns to insert at each interval? ", xTitleId, 1, Type:=1) xNum1 = WorkRng.Column + xInterval xNum2 = xColumns + xInterval Set xWs = WorkRng.Parent For i = 1 To Int(xColumnsCount / xInterval) xWs.Range(xWs.Cells(WorkRng.Row, xNum1), xWs.Cells(WorkRng.Row, xNum1 + xColumns - 1)).Select Application.Selection.EntireColumn.Insert xNum1 = xNum1 + xNum2 Next End Sub
3. Press the F5 key to run this code. A prompt box pops out to remind you to select the data range in which you want to insert blank columns.
4. Click the OK button. Another prompt box pops out, please enter the number of columns intervals in the Enter column interval box.
5. Continue to click the OK button. The third prompt box pops out, please specify the number of columns you want to insert at each interval in the box.
6. Click the OK button to get the result.
If you don’t want to use the VBA code method, the Insert Blank Rows & Columns feature of Kutools for Excel is the perfect solution for you. With this feature, you can insert the specific number of columns into data range at fixed intervals in just a few clicks.
Before applying Kutools for Excel, please download and install it firstly.
1. Select the existing data range first. Then click Kutools > Insert > Insert Blank Rows & Columns.
2. The Insert Blank Rows & Columns dialog box pops up. You can see the data range you selected in step 1 displayed in the Range box. Choose the Blank columns option in the Insert type section. And specify the numbers of columns intervals and columns you want to insert at each interval. Here I input 1 and 3 separately.
3. Click the OK button to finish the setting and get the result.
Note: If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.