insert with data

[KiranKM]

[FONT=&quot]I have following table[/FONT]
[FONT=&quot]header1
row1
row2
row3[/FONT]
[FONT=&quot]need to convert as below[/FONT]
[FONT=&quot]header1
row1
header1
row2
header1
row3[/FONT]
[FONT=&quot]Can anyone share code for this….note the row count is changing every time you insert[/FONT]
[FONT=&quot]Thanks.[/FONT]

 

[tygrrboi]

Does this do what you want?

Sub Convert_With_Headers()
Dim D As Variant
Dim S As String
Dim i As Integer, count As Integer


With ActiveSheet
    S = .Range("A1").Value  'the header location
    D = .Range("A2:A" & .Cells(.Rows.count, "A").End(xlUp).Row)
    count = 1


    For i = 1 To 2 * UBound(D) Step 2
        .Range("A" & i).Value = S
        .Range("A" & i + 1).Value = D(count, 1)
        count = count + 1
    Next
End With
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

In case you may have formulas, or formatting, or more than one column of data, you might also consider this alternative.

Sub InsertHeaders()
  Dim rws As Long, nc As Long
  
  Application.ScreenUpdating = False
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  With Range("A2", Range("A" & Rows.count).End(xlUp)).Resize(, nc)
    rws = .Rows.count
    .Columns(nc).Value = Evaluate("row(" & .Address & ")")
    .Columns(nc).Offset(rws).Resize(rws - 1).Value = .Columns(nc).Value
    With .Resize(2 * rws - 1)
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Columns(1).SpecialCells(xlBlanks).Value = .Cells(0, 1).Value
      .Columns(nc).ClearContents
    End With
  End With
  Application.ScreenUpdating = True
End Sub

Before:

Excel Workbook
	A	B	C
1	header1	header2	header3
2	row1	data1
3	row2	data2
4	row3	data3	something
5	row4	data4
6	row5	data5
7	row6	data6
8	row7	data7
9	row8	data8
10	row9	data9
11	row10	data10
12	row11	data11
Insert repeat headers

After:

Excel Workbook
	A	B	C
1	header1	header2	header3
2	row1	data1
3	header1
4	row2	data2
5	header1
6	row3	data3	something
7	header1
8	row4	data4
9	header1
10	row5	data5
11	header1
12	row6	data6
13	header1
14	row7	data7
15	header1
16	row8	data8
17	header1
18	row9	data9
19	header1
20	row10	data10
21	header1
22	row11	data11
Insert repeat headers

 

[KiranKM]

Thank you Peter_SSs and tygrrboi .

 

[Peter_SSs]

Thank you Peter_SSs and tygrrboi .

You are welcome.