Note: The other languages of the website are Google-translated. Back to English

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.

Insert specific number of blank columns into data range at fixed intervals with VBA code

Insert specific number of blank columns into data range at fixed intervals with an amazing feature


Insert specific number of blank columns into data range at fixed intervals with VBA code

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.
doc insert-columns-at-intervals 1

4. Click the OK button. Another prompt box pops out, please enter the number of columns intervals in the Enter column interval box.
doc insert-columns-at-intervals 2

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.
doc insert-columns-at-intervals 3

6. Click the OK button to get the result.
doc insert-columns-at-intervals 4


Insert specific number of blank columns into data range at fixed intervals with an amazing feature

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.
doc insert-columns-at-intervals 5

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.
doc insert-columns-at-intervals 6

3. Click the OK button to finish the setting and get the result.
doc insert-columns-at-intervals 7

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.


Other Operations (Articles)

How To Quickly Insert A Line Based On Current Date In Excel?
Supposing there is a sheet containing a row with dates, and now I want to insert a line right to the current date which will auto change while opening the workbook every day. Is there a trick can solve it in Excel?

How To Always Insert A Blank Row Below Instead Of Above In Excel?
As we all known, when inserting blank row in a worksheet, the blank row will always be inserted above the selected row or cell. But, sometimes, you may need to insert the row below the selected cell or row. How could you solve this job in Excel?

How To Insert A Blank New Row Automatically By Command Button In Excel?
In many cases, you may need to insert a blank new row into a specified position of your worksheet. In this article, we will show you how to insert a blank new row automatically by clicking on a Command Button in Excel.

How To Insert A Blank Row After Specific Text In Excel?
If you want to insert blank rows after a specific text as following screenshot shown, how to deal with it quickly and easily without inserting them manually one by one?



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations