Forum Discussion
Solved
my excel issue
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 betwe...
- 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
Inserting 13 blank rows below each row won't work?
- is there any formula or function or is it just impossible ?
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 SubChange 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
HansVogelaar 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 ?