VBA Macro Transpose/Delete Rows and continue down spreadsheet with differing total number of rows

[krishna008]

Hi All,

I have a little bit of VBA knowledge but am struggling with this one.

I have a data extraction from an accounting system. The first half of the data is represented horizontally as expected, but the second half of the data is somehow expressed vertically (a can of worms I don't need to go into right now).

I need a way to transpose this vertical data to the corresponding row, delete the rows no longer required, then continue for the next record and so on. This data will having varying rows so ideally need to make sure it can handle that.

Here is a screenshot of the spreadsheet to show you what I mean but I can attach a truncated copy of the file if needed.
First I need just one version of the data in AB2:AB55 to copies and transposed to AD1 to provide headings.
Then need AC2:AC55 to be transposed to AD2. I then need rows 3:55 to be deleted.
Then need to repeat the same for the next WOnumber (which is in column A) but it will be on the next row down (AC3:56 transposed to AD3, then delete 4:56 to be deleted and so on).

I go the vb macro code from the mr.excel forum, however my sheet has the empty values in the AC column. and also I have the different data types. I want to copy what ever there under AC column and transpose them under AD.

I will be adding some formatting to the code but I can add this myself

Thank you in advance!

Data sheet. unable to post entire sheet due to restriction of 300 cells, but I will have the 100+ WO# and corresponding data in the other cells. Here I gave only one WO# example.

Input.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	WO#	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11	Header12	Header13	Header14	Header15	Header16	Header17	Header18	Header19	Header20	Header21	Header22	Header23	Header24	Header25	Header26	Question	Answer
2	1234	Row1.01	Row1.02	Row1.03	Row1.04	Row1.05	Row1.06	Row1.07	Row1.08	Row1.09	Row1.10	Row1.11	Row1.12	Row1.13	Row1.14	Row1.15	Row1.16	Row1.17	Row1.18	Row1.19	Row1.20	Row1.21	Row1.22	Row1.23	Row1.24	Row1.25	Row1.26	Question1	Answer1
3																												Question2	Answer2
4																												Question3	Answer3
5																												Question4	Answer4
6																												Question5	Answer5
7																												Question6	Answer6
8																												Question7	Answer7
9																												Question8	Answer8
10																												Question9	Answer9
11																												Question10	Answer10
12																												Question11	Answer11
13																												Question12	Answer12
14																												Question13	Answer13
15																												Question14	Answer14
16																												Question15	Answer15
17																												Question16	Answer16
18																												Question17	Answer17
19																												Question18	Answer18
20																												Question19	Answer19
21																												Question20	Answer20
22																												Question21	Answer21
23																												Question22	Answer22
24																												Question23	Answer23
25																												Question24	Answer24
26																												Question25	Answer25
27																												Question26
28																												Question27	Answer27
29																												Question28	Answer28
30																												Question29	Answer29
31																												Question30	Answer30
32																												Question31	Answer31
33																												Question32	Answer32
34																												Question33	Answer33
35																												Question34	Answer34
36																												Question35	Answer35
37																												Question36	Answer36
38																												Question37	Answer37
39																												Question38	Answer38
40																												Question39	Answer39
41																												Question40	Answer40
42																												Question41
43																												Question42	Answer42
44																												Question43	Answer43
45																												Question44
46																												Question45	Answer45
47																												Question46	Answer46
48																												Question47	Answer47
49																												Question48	Answer48
50																												Question49	Answer49
51																												Question50	Answer50
52																												Question51	Answer51
53																												Question52	Answer52
54																												Question53	Answer53
55																												Question54	Answer54
Data

Expected Reusult:

Macro.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE
1	WO#	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11	Header12	Header13	Header14	Header15	Header16	Header17	Header18	Header19	Header20	Header21	Header22	Header23	Header24	Header25	Header26	Question	Answer	Question1	Question2	Question3	Question4	Question5	Question6	Question7	Question8	Question9	Question10	Question11	Question12	Question13	Question14	Question15	Question16	Question17	Question18	Question19	Question20	Question21	Question22	Question23	Question24	Question25	Question26	Question27	Question28	Question29	Question30	Question31	Question32	Question33	Question34	Question35	Question36	Question37	Question38	Question39	Question40	Question41	Question42	Question43	Question44	Question45	Question46	Question47	Question48	Question49	Question50	Question51	Question52	Question53	Question54
2	1234	Row1.01	Row1.02	Row1.03	Row1.04	Row1.05	Row1.06	Row1.07	Row1.08	Row1.09	Row1.10	Row1.11	Row1.12	Row1.13	Row1.14	Row1.15	Row1.16	Row1.17	Row1.18	Row1.19	Row1.20	Row1.21	Row1.22	Row1.23	Row1.24	Row1.25	Row1.26	Question1	Answer1	Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Answer7	Answer8	Answer9	Answer10	Answer11	Answer12	Answer13	Answer14	Answer15	Answer16	Answer17	Answer18	Answer19	Answer20	Answer21	Answer22	Answer23	Answer24	Answer25		Answer27	Answer28	Answer29	Answer30	Answer31	Answer32	Answer33	Answer34	Answer35	Answer36	Answer37	Answer38	Answer39	Answer40		Answer42	Answer43		Answer45	Answer46	Answer47	Answer48	Answer49	Answer50	Answer51	Answer52	Answer53	Answer54
Sheet1

I will delete the original AB:AC columns using the "columns("AB:AC").Delete" option.

This is the code I got it from the Mr.Excel forum, but it does not work if the AC column has many empty values.

VBA Macro Transpose/Delete Rows and continue down spreadsheet with differing total number of rows

Hi All, I have a little bit of VBA knowledge but am struggling with this one. I have a data extraction from an accounting system. The first half of the data is represented horizontally as expected, but the second half of the data is somehow expressed vertically (a can of worms I don't need to...

www.mrexcel.com

 

[johnnyL]

I think the following does what you asked for:

Sub TransposeAndPaste()
'
    Dim LoopCount                   As Long, NumberOfWOs                    As Long
    Dim DataSheetColumn_AB_Array    As Variant, DataSheetColumn_AC_Array    As Variant
'
    Application.ScreenUpdating = False
'
    With Sheets("Data")
        DataSheetColumn_AB_Array = Application.Transpose(.Range("AB2:AB55").Value2)                                 '   Save "Data" sheet column AB values from rows 2:55 into DataSheetColumn_AB_Array
'
        .Range("AD1").Resize(1, UBound(DataSheetColumn_AB_Array, 1)) = DataSheetColumn_AB_Array                     '   Paste the transposed data from Column AB to the horizontal range
'
        NumberOfWOs = .Range("A" & Rows.Count).End(xlUp).Row - 1                                                    '   Get the # of work orders from Column A in 'Data' sheet
'
        For LoopCount = 1 To NumberOfWOs                                                                            '   Loop through each work order in Column A
            DataSheetColumn_AC_Array = Application.Transpose(.Range("AC2:AC55").Value2)                             '       Save "Data" sheet column AC values from rows 2:55 into DataSheetColumn_AC_Array
            .Range("AD" & LoopCount + 1).Resize(1, UBound(DataSheetColumn_AC_Array, 1)) = DataSheetColumn_AC_Array  '       Paste the transposed data from Column AC to the horizontal range
'
            .Range("AC2:AC55").Delete Shift:=xlUp                                                                   '       Delete Previous transposed data range since it is no longer needed
        Next                                                                                                        '   Loop back
'
        .Columns("AB:AC").Delete                                                                                    '   Delete the Columns that were transposed
    End With
'
    Application.ScreenUpdating = True
End Sub

 

[krishna008]

Thanks for your quick reply. I tried the macro, it does transpose the first and second rows of the question data, but failed to bring the rows from A:AA from the second work order onwards. the second row values are still in their original row. we want to move them back to top to aglign with the transposed data.
here is the result:

Macro.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC
1	WO#	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11	Header12	Header13	Header14	Header15	Header16	Header17	Header18	Header19	Header20	Header21	Header22	Header23	Header24	Header25	Header26	Question1	Question2	Question3	Question4	Question5	Question6	Question7	Question8	Question9	Question10	Question11	Question12	Question13	Question14	Question15	Question16	Question17	Question18	Question19	Question20	Question21	Question22	Question23	Question24	Question25	Question26	Question27	Question28	Question29	Question30	Question31	Question32	Question33	Question34	Question35	Question36	Question37	Question38	Question39	Question40	Question41	Question42	Question43	Question44	Question45	Question46	Question47	Question48	Question49	Question50	Question51	Question52	Question53	Question54
2	1234	Row1.01	Row1.02	Row1.03	Row1.04	Row1.05	Row1.06	Row1.07	Row1.08	Row1.09	Row1.10	Row1.11	Row1.12	Row1.13	Row1.14	Row1.15	Row1.16	Row1.17	Row1.18	Row1.19	Row1.20	Row1.21	Row1.22	Row1.23	Row1.24	Row1.25	Row1.26	Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Answer7	Answer8	Answer9	Answer10	Answer11	Answer12	Answer13	Answer14	Answer15	Answer16	Answer17	Answer18	Answer19	Answer20	Answer21	Answer22	Answer23	Answer24	Answer25		Answer27	Answer28	Answer29	Answer30	Answer31	Answer32	Answer33	Answer34	Answer35	Answer36	Answer37	Answer38	Answer39	Answer40		Answer42	Answer43		Answer45	Answer46	Answer47	Answer48	Answer49	Answer50	Answer51	Answer52	Answer53	Answer54
3																												Answer55	Answer56	Answer57	Answer58	Answer59	Answer60		Answer62		Answer64	Answer65	Answer66		Answer68	Answer69	Answer70	Answer71	Answer72	Answer73	Answer74	Answer75	Answer76	Answer77	Answer78	Answer79	Answer80	Answer81	Answer82	Answer83	Answer84	Answer85		Answer87	Answer88	Answer89	Answer90	Answer91	Answer92	Answer93	Answer94	Answer95	Answer96	Answer97	Answer98	Answer99	Answer100	Answer101	Answer102		Answer104	Answer105		Answer107	Answer108
Data

Second WO# row left alone

Macro.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
56	1235	Row2.01	Row2.02	Row2.03	Row2.04	Row2.05	Row2.06	Row2.07	Row2.08	Row2.09	Row2.10	Row2.11	Row2.12	Row2.13	Row2.14	Row2.15	Row2.16	Row2.17	Row2.18	Row2.19	Row2.20	Row2.21	Row2.22	Row2.23	Row2.24	Row2.25	Row2.26
Data

 

[johnnyL]

Sorry, I'm not sure what you are saying.

The following is the data that I m starting with:
Part 1:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	WO#	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11	Header12	Header13	Header14	Header15	Header16	Header17	Header18	Header19	Header20	Header21	Header22	Header23	Header24	Header25	Header26	Question	Answer
2	1234	Row2.01	Row2.02	Row2.03	Row2.04	Row2.05	Row2.06	Row2.07	Row2.08	Row2.09	Row2.10	Row2.11	Row2.12	Row2.13	Row2.14	Row2.15	Row2.16	Row2.17	Row2.18	Row2.19	Row2.20	Row2.21	Row2.22	Row2.23	Row2.24	Row2.25	Row2.26	Question1	Answer1
3	5678	Row3.01	Row3.02	Row3.03	Row3.04	Row3.05	Row3.06	Row3.07	Row3.08	Row3.09	Row3.10	Row3.11	Row3.12	Row3.13	Row3.14	Row3.15	Row3.16	Row3.17	Row3.18	Row3.19	Row3.20	Row3.21	Row3.22	Row3.23	Row3.24	Row3.25	Row3.26	Question2	Answer2
Original

Part2:

Book2
	AB	AC	AD
1	Question	Answer
2	Question1	Answer1
3	Question2	Answer2
4	Question3	Answer3
5	Question4	Answer4
6	Question5	Answer5
7	Question6	Answer6
8	Question7	Answer7
9	Question8	Answer8
10	Question9	Answer9
11	Question10	Answer10
12	Question11	Answer11
13	Question12	Answer12
14	Question13	Answer13
15	Question14	Answer14
16	Question15	Answer15
17	Question16	Answer16
18	Question17	Answer17
19	Question18	Answer18
20	Question19	Answer19
21	Question20	Answer20
22	Question21	Answer21
23	Question22	Answer22
24	Question23	Answer23
25	Question24	Answer24
26	Question25	Answer25
27	Question26
28	Question27	Answer27
29	Question28	Answer28
30	Question29	Answer29
31	Question30	Answer30
32	Question31	Answer31
33	Question32	Answer32
34	Question33	Answer33
35	Question34	Answer34
36	Question35	Answer35
37	Question36	Answer36
38	Question37	Answer37
39	Question38	Answer38
40	Question39	Answer39
41	Question40	Answer40
42	Question41
43	Question42	Answer42
44	Question43	Answer43
45	Question44
46	Question45	Answer45
47	Question46	Answer46
48	Question47	Answer47
49	Question48	Answer48
50	Question49	Answer49
51	Question50	Answer50
52	Question51	Answer51
53	Question52	Answer52
54	Question53	Answer53
55	Question54	Answer54
56	Question1A	Answer1A
57	Question2A	Answer2A
58	Question3A	Answer3A
59	Question4A	Answer4A
60	Question5A	Answer5A
61	Question6A	Answer6A
62	Question7A	Answer7A
63	Question8A	Answer8A
64	Question9A	Answer9A
65	Question10A	Answer10A
66	Question11A	Answer11A
67	Question12A	Answer12A
68	Question13A	Answer13A
69	Question14A	Answer14A
70	Question15A	Answer15A
71	Question16A	Answer16A
72	Question17A	Answer17A
73	Question18A	Answer18A
74	Question19A	Answer19A
75	Question20A	Answer20A
76	Question21A	Answer21A
77	Question22A	Answer22A
78	Question23A	Answer23A
79	Question24A	Answer24A
80	Question25A	Answer25A
81	Question26A	Answer26A
82	Question27A	Answer27A
83	Question28A	Answer28A
84	Question29A	Answer29A
85	Question30A	Answer30A
86	Question31A	Answer31A
87	Question32A	Answer32A
88	Question33A	Answer33A
89	Question34A	Answer34A
90	Question35A	Answer35A
91	Question36A	Answer36A
92	Question37A	Answer37A
93	Question38A	Answer38A
94	Question39A	Answer39A
95	Question40A	Answer40A
96	Question41A
97	Question42A	Answer42A
98	Question43A	Answer43A
99	Question44A
100	Question45A	Answer45A
101	Question46A	Answer46A
102	Question47A	Answer47A
103	Question48A	Answer48A
104	Question49A	Answer49A
105	Question50A	Answer50A
106	Question51A	Answer51A
107	Question52A	Answer52A
108	Question53A	Answer53A
109	Question54A	Answer54A
110
Original

The reult I get after running the code is:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD
1	WO#	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11	Header12	Header13	Header14	Header15	Header16	Header17	Header18	Header19	Header20	Header21	Header22	Header23	Header24	Header25	Header26	Question1	Question2	Question3	Question4	Question5	Question6	Question7	Question8	Question9	Question10	Question11	Question12	Question13	Question14	Question15	Question16	Question17	Question18	Question19	Question20	Question21	Question22	Question23	Question24	Question25	Question26	Question27	Question28	Question29	Question30	Question31	Question32	Question33	Question34	Question35	Question36	Question37	Question38	Question39	Question40	Question41	Question42	Question43	Question44	Question45	Question46	Question47	Question48	Question49	Question50	Question51	Question52	Question53	Question54
2	1234	Row2.01	Row2.02	Row2.03	Row2.04	Row2.05	Row2.06	Row2.07	Row2.08	Row2.09	Row2.10	Row2.11	Row2.12	Row2.13	Row2.14	Row2.15	Row2.16	Row2.17	Row2.18	Row2.19	Row2.20	Row2.21	Row2.22	Row2.23	Row2.24	Row2.25	Row2.26	Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Answer7	Answer8	Answer9	Answer10	Answer11	Answer12	Answer13	Answer14	Answer15	Answer16	Answer17	Answer18	Answer19	Answer20	Answer21	Answer22	Answer23	Answer24	Answer25		Answer27	Answer28	Answer29	Answer30	Answer31	Answer32	Answer33	Answer34	Answer35	Answer36	Answer37	Answer38	Answer39	Answer40		Answer42	Answer43		Answer45	Answer46	Answer47	Answer48	Answer49	Answer50	Answer51	Answer52	Answer53	Answer54
3	5678	Row3.01	Row3.02	Row3.03	Row3.04	Row3.05	Row3.06	Row3.07	Row3.08	Row3.09	Row3.10	Row3.11	Row3.12	Row3.13	Row3.14	Row3.15	Row3.16	Row3.17	Row3.18	Row3.19	Row3.20	Row3.21	Row3.22	Row3.23	Row3.24	Row3.25	Row3.26	Answer1A	Answer2A	Answer3A	Answer4A	Answer5A	Answer6A	Answer7A	Answer8A	Answer9A	Answer10A	Answer11A	Answer12A	Answer13A	Answer14A	Answer15A	Answer16A	Answer17A	Answer18A	Answer19A	Answer20A	Answer21A	Answer22A	Answer23A	Answer24A	Answer25A	Answer26A	Answer27A	Answer28A	Answer29A	Answer30A	Answer31A	Answer32A	Answer33A	Answer34A	Answer35A	Answer36A	Answer37A	Answer38A	Answer39A	Answer40A		Answer42A	Answer43A		Answer45A	Answer46A	Answer47A	Answer48A	Answer49A	Answer50A	Answer51A	Answer52A	Answer53A	Answer54A
4
Data

Is your data arranged differently? Please further explain what the code I submitted is not doing for you.