Forum Discussion

benbomb's avatar
benbomb
Tin Contributor
Jul 10, 2023
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...
excel
Formulas and Functions
  • benbomb's avatar
    benbomb
    Jul 11, 2023
    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
benbomb's avatar
benbomb
Tin Contributor
Jul 11, 2023
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