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
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 ?
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?