my excel issue

[benbomb]

I am a bit of a beginner to excel and have an issue. I need to insert a fixed amount of cells in between every cell of 9 separate, very long columns. I don't want to manually insert 13 cells in between around 45000 cells as that would take far to long.

[#2]

@benbomb 

Inserting 13 blank rows below each row won't work?

[#3]

is there any formula or function or is it just impossible ?

[#4]

@benbomb 

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Sub InsertCells()
    Dim c As Variant
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    ' Change the columns as needed
    For Each c In Array("B", "D", "G", "H", "K", "N", "Z", "AD", "AG")
        m = Range(c & Rows.Count).End(xlUp).Row
        For r = m To 2 Step -1
            Range(c & r).Resize(13).Insert Shift:=xlShiftDown
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub

Change the columns to the one you want.

With the insertion point anywhere in the code, press F5 to run it. It will take some time.

Switch back to Excel

[#5]

@Hans Vogelaar I have been given a message saying "run-time error '1004': insert method of Range class failed" as there is a bug on line 10. Do you know what is wrong ?

[#6]

@benbomb 

The code works when I try it. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#7 · Accepted solution]

Sub InsertCells()
Dim m As Long
Dim n As Long
Dim v As Variant
Dim r As Long
Dim c As Variant
Application.Cursor = xlWait
Application.ScreenUpdating = False
Cells.Replace What:=" ", Replacement:="", LookAt:=xlWhole
m = Range("A" & Rows.Count).End(xlUp).Row
n = Range("B" & Rows.Count).End(xlUp).Row
v = Range("A1:P" & m).Value
For r = n To 9 Step -1
For Each c In Array(2, 3, 4, 6, 7, 8, 10, 11, 12, 14, 15, 16)
v(13 * r - 96, c) = v(r, c)
v(r, c) = ""
Next c
Next r
Range("A1:P" & m).Value = v
Application.ScreenUpdating = True
Application.Cursor = xlDefault
End Sub

this is the VBA used to solve my issue, for anyone who has similar issues

[#8]

@benbomb 

(Code provided by me via PM)