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 between around 45000 cells as that would take far to long.

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

7 Replies

  • 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
    • benbomb's avatar
      benbomb
      Tin Contributor
      Jul 10, 2023
      is there any formula or function or is it just impossible ?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 10, 2023

        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