columns to rows macros

[srisansan]

I have a question on how to convert columns to rows, i tried and my macros was able to get most except for the header row. I would appreciate if anyone can help me where i'm having issues in my code. Also how do i upload my excel if i want to share what i'm trying to explain.


My data is like this

[pre]

Brand	Store	Name	open Date	close Date	200401	200402	200403	200404
SS1	1	ABC	01/01/2002		 C 	 C 	 Relo 	 Relo
SS1	2	DEF	12/01/2002		 C 	 C 	 C 	 C
SS1	3	DEF	07/08/2004				 C 	 C
I want my output to be

[b]Output

[pre][code]Brand	Store	Name	Open Date	Close Date	MonthYr	Status
SS1	1	ABC	01/01/2002		200401	 C
SS1	1	ABC	01/01/2002		200402	 C
SS1	1	ABC	01/01/2002		200403	 Relo
SS1	1	ABC	01/01/2002		200404	 Relo
SS1	2	DEF	12/01/2002		200401	 C
SS1	2	DEF	12/01/2002		200402	 C
SS1	2	DEF	12/01/2002		200403	 C
SS1	2	DEF	12/01/2002		200404	 C
SS1	3	DEF	07/08/2004		200403	 C
SS1	3	DEF	07/08/2004		200404	 C

[/pre]
My Macro[/b]

Sub test()
lastrow = Range("A" & Rows.Count).End(xlUp).Row
destrow = lastrow + 1
For i = 2 To lastrow

For Each Cell In Range("F" & i & ":Q" & i)
If Cell <> "" Then
Cells(destrow, 1).Value = Cells(i, 1).Value
Cells(destrow, 2).Value = Cells(i, 2).Value
Cells(destrow, 3).Value = Cells(i, 3).Value
Cells(destrow, 4).Value = Cells(i, 4).Value
If Cells(i, 5).Value = "" Then
Cells(destrow, 5).Value = ""
Else
Cells(destrow, 5).Value = Cells(i, 5).Value
End If
'need the header 200401 .. here
'Cells(destrow,6) = "Header"
Cells(destrow, 7) = Cell.Value
destrow = destrow + 1
End If
Next Cell

Next i

End Sub[/code][/pre]
Thanks in Advance

 

[vasim]

I'm not sure if Ninja can help.....but can you post a workbook...


http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[srisansan]

Please find the Link here


https://docs.google.com/folder/d/0Bwmvvb0YfRNBNTdlanE0blRvMVU/edit?usp=sharing


Thanks

Sri

 

[vasim]

I am not able to download the file...don't know y....


Just looking at it, i would suggest you prepare a Pivot table and then fill the blanks range, something explained here...


http://chandoo.org/wp/2011/10/17/fill-blank-cells-in-a-table/

 

[NARAYANK991]

Hi ,


You can download from here :


https://docs.google.com/file/d/0B0KMpuzr3MTVdUZ4MklMNzBMR28/edit?usp=sharing


Narayan

 

[srisansan]

I hope my excel was clearer, whenever my columns are blank i want those to be ignored and only the columns with Data to be transposed with corresponding header lines. I'm able to do all those but not able to get the header value as a column


thanks

 

[shrivallabha]

This will get you header row. It is good practice to declare variables.

[pre]

Sub test2()
Dim cell As Range
Dim lastrow As Long, i As Long

Application.ScreenUpdating = False

lastrow = Range("A" & Rows.Count).End(xlUp).Row
destrow = lastrow + 1

For i = 2 To lastrow
For Each cell In Range("F" & i & ":Q" & i)
If cell <> "" Then
Cells(destrow, 1).Value = Cells(i, 1).Value
Cells(destrow, 2).Value = Cells(i, 2).Value
Cells(destrow, 3).Value = Cells(i, 3).Value
Cells(destrow, 4).Value = Cells(i, 4).Value
If Cells(i, 5).Value = "" Then
Cells(destrow, 5).Value = ""
Else
Cells(destrow, 5).Value = Cells(i, 5).Value
End If
Cells(destrow, 6) = Cells(1, cell.Column)
Cells(destrow, 7) = cell.Value
destrow = destrow + 1
End If
Next cell
Next i

Application.ScreenUpdating = True

End Sub

[/pre]