Insert Rows Between Specific Rows in Excel

Question

By using Data from another sheet I need to insert rows.

For example, if there are data in column 10 using values from those cells as row number I need to insert columns.

This is the code I have and it wont work

Sheets("Input").Select
Rows("11:11").Select
Selection.Copy

Dim x As Integer
For x = 1 To 12

Dim Hdr As Integer

Hdr = Worksheets("Input").Cells(10, x).Value

Sheets("Data Table").Select
Rows("Hdr:Hdr").Select
Selection.Insert Shift:=xlDown
Next x

What about this post: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Dominique, Jan 03 '23 at 12:07
Could you share the screenshot of the `Data Table` worksheet and better explain what needs to be done? It looks like you're mixing rows and columns. — VBasic2008, Jan 03 '23 at 12:13
" if there are data in **column 10**" ? `Hdr = Worksheets("Input").Cells(10, x).Value` refers to **row 10**. "I need to insert columns." ? `Rows("Hdr:Hdr").Select: Selection.Insert Shift:=xlDown` is inserting **rows**. Probably should be just `Rows(Hdr).Select`. — CDP1802, Jan 03 '23 at 12:19
Yes it was rows I needed to insert. thank you for your help. your code works! — James Fenix, Jan 04 '23 at 03:10

score 1 · Accepted Answer · answered Jan 03 '23 at 12:28

avoid Select/Selection and use explicit references to object use Rows(Hdr)

With Worksheets("Input") ' reference "Input" sheet

    .Rows("11:11").Copy ' copy referenced sheet row 11

    Dim x As Long
    For x = 1 To 12
    
        Dim Hdr As Long
        Hdr = .Cells(10, x).Value ' store the value in referenced sheet cell in row 10 and column x
        
        Sheets("Data Table").Rows(Hdr).Insert Shift:=xlDown
    Next

End With

Insert Rows Between Specific Rows in Excel

1 Answers1