Solved Insert rows based on column count

Microsoft Excel 2010 - complete product...
December 5, 2016 at 13:26:13
Specs: Windows 7
I have data that I would like to write vba code to reformat. Specifically, I need to copy and transpose column data into rows. However, I do not want the transpose paste to override existing data. As an example the data is formatted as follows:

Supplier Code I Contract ID I Buyer I Additional Supplier Codes
xxxxxx I 4259 I John I vvvvvv I zzzz I yyyy I

I need to list out each additional supplier code as its own row and drag down the Contract ID and Buyer for all newly created rows.

I'm having trouble transposing the additional supplier codes without overriding existing data

Also, inserting the correct number of rows based on how many additional supplier codes exist, which varies, some have none some have up to 25.

Thoughts?

message edited by elaloz06


See More: Insert rows based on column count

Report •

#1
December 6, 2016 at 12:15:51
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions
on how to format example data so that it is easier for us to read.

AS for your question...

I started with the following table. (I shortened your column headers just to make
them fit in the table in this post)

Column D:H are the additional suppliers. I stopped at H, but it doesn't matter how additional suppliers you have.

	   A	  B	 C	D	E	F	G	H
1	SupCode  CID  	Buyer 	     Additional Supplier Codes				
2	xxxxxx 	4259	John	1	2			
3	xxxxxx 	4260	Bob	1	2	3		
4	xxxxxx 	4261	Sue	1	2	3	4	
5	xxxxxx 	4262	Mary	1	2	3	4	5
6	xxxxxx 	4263	Fred	1	2	3	4	
7	xxxxxx 	4264	Carl	1	2	3	4	5
8	xxxxxx 	4265	Lisa					
9	xxxxxx 	4266	Heather	1				
10	xxxxxx 	4267	Stu	1	2			


I ran the following code and ended up with the table at the
end of this post. Is that what you wanted?

Sub TransposeData()

Application.ScreenUpdating = False
'Determine last row with data in Column A
 lastRw = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through rows in reverse order
  For rw = lastRw To 2 Step -1
  
'Count additional suppliers
   numSuppliers = Cells(rw, Columns.Count).End(xlToLeft).Column - 3
      If numSuppliers > 0 Then
'Copy current Row
        Cells(rw, 1).EntireRow.Copy
'Insert-Paste based on number of Additional Suppliers
        Range(Cells(rw, 1), Cells(rw + numSuppliers - 1, 1)).Insert
        
'Copy/Transpose Additional Suppliers from D:LastCol  to Column A
        Range(Cells(rw, 4), Cells(rw, 4 + numSuppliers - 1)).Copy
        Cells(rw + 1, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
      End If
 Next
 
'Delete Additional Suppliers from D:LastCol
    lastRw = Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(1, 4), Cells(lastRw, Columns.Count)).ClearContents

End Sub


	    A	 B	  C
1	SupCode	CID     Buyer 
2	xxxxxx 	4259	John
3	1	4259	John
4	2	4259	John
5	xxxxxx 	4260	Bob
6	1	4260	Bob
7	2	4260	Bob
8	3	4260	Bob
9	xxxxxx 	4261	Sue
10	1	4261	Sue
11	2	4261	Sue
12	3	4261	Sue
13	4	4261	Sue
14	xxxxxx 	4262	Mary
15	1	4262	Mary
16	2	4262	Mary
17	3	4262	Mary
18	4	4262	Mary
19	5	4262	Mary
20	xxxxxx 	4263	Fred
21	1	4263	Fred
22	2	4263	Fred
23	3	4263	Fred
24	4	4263	Fred
25	xxxxxx 	4264	Carl
26	1	4264	Carl
27	2	4264	Carl
28	3	4264	Carl
29	4	4264	Carl
30	5	4264	Carl
31	xxxxxx 	4265	Lisa
32	xxxxxx 	4266	Heather
33	1	4266	Heather
34	xxxxxx 	4267	Stu
35	1	4267	Stu
36	2	4267	Stu

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 6, 2016 at 14:12:58
Works like a charm! Thanks so much!

Report •
Related Solutions


Ask Question