Repeating Column header for each row

[jamex]

How to repeat column header for each row

NAME JOB SALARY
AAA CLERK 5000
BBB MANAGER 6000
CCC CASHIER 7000


I want result as:

NAME JOB SALARY
AAA CLERK 5000
NAME JOB SALARY
BBB MANAGER 6000
NAME JOB SALARY
CCC CASHIER 700

 

[Guest]

Try:

Sub a()
Dim title() As String
With Worksheets("Sheet1") '<=== change as needed
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
titles = .Range("a1:c1")
For r = lastrow To 3 Step -1
.Cells(r, 1).EntireRow.Insert
.Cells(r, 1).Resize(1, 3) = titles
Next r
End With
End Sub

 

[Dave Peterson]

First, I think keeping the data in a nice tabular form is usually much better.

There are lots of things that will become more difficult if you do this.

But if you want, you can use a macro:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HeaderRow As Long

Set wks = Worksheets("sheet1")
With wks
HeaderRow = 1
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
.Rows(HeaderRow).Copy
.Rows(iRow).Insert
Next iRow
End With
Application.CutCopyMode = False
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[jamex]

Thanks topper, nice coding.

Can we have other option to solve this problem other than you macro?

 

[Jim Cone]

First...
In an adjacent column, consecutively number the rows and then repeat.
Sort the data by the numbers to add a blank row between each row.

NAME JOB SALARY
1 AAA CLERK 5000
2 BBB MANAGER 6000
3 CCC CASHIER 7000
4 DDD WIFE 8000
5 EEE CHILD 9000
1
2
3
4
5

Second...
Copy the titles.
Third...
Select the column with the names (not the entire column)
Fourth...
Edit | GoTo | Special | Blanks
Fifth...
Paste
Sixth...
Delete the extra column
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



in message
How to repeat column header for each row

NAME JOB SALARY
AAA CLERK 5000
BBB MANAGER 6000
CCC CASHIER 7000

I want result as:

NAME JOB SALARY
AAA CLERK 5000
NAME JOB SALARY
BBB MANAGER 6000
NAME JOB SALARY
CCC CASHIER 7000

 

[vezerid]

jamex,

you can do it with formulas. Assuming your data (first header, NAME)
starts at A1, and assuming that your output starts at E1, you can use
the following formula in E1:

=IF(MOD(ROW()-ROW(E$1),2)=0,A$1,OFFSET(A$1,1+(ROW()-ROW(E$1))/2,0))

This formula can be copied down and to the right, as far as necessary.
Replace A1 and E1 to suit.

HTH
Kostis Vezerides