VBA macro, move cell contents, duplicate down if additional value, delete empty rows

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi lengwer,
. This sort of problem is technically very simple but just requires a lot of careful looping to rearrange to the order in which you want the output.
. Here is a start. It basically does what it appears you want from the info you gave. Note however I am using a VBA Array approach in this example. It is a quick, favoured method but it does not maintain formatting.
. To preserve Formatting you would need to use a slower spreadsheet interaction method. But the basic idea / looping would be the same. Try this for now and when you feedback we can take it from there if you want it done differently. ( I have, for this example, arranged that the output goes to a newly made sheet with the name “Output” )
........
.. So, my code takes this:

Using Excel 2007

-	A	B	C	D	E	F	G	H	I
1	Sales Order Comparison Exceptions
2							Run Date:	08/06/2015
3				Original Order Q’ty			Remaining Balance Due
4	Line #	Item	Description	Per Deb	Per G	Variance	Per Deb	Per G	Variance
5
6	Order #	1000022
7	1	164B1822G001		0	480	(480)	0	480	(480)
8	1	123A1822H001		2520	480	2040	0	480	(480)
9	Order Totals			2520	960	1560	0	960	(960)
10
11	Order #	1000070
12	1	1H4Y1387J004	ROD ANODE-50G	0	840	(840)	0	(40)	40
13	Order Totals			0	840	(840)	0	(40)	40
14
15	Order #	1000139
16	1	194J1734K002	BLADE FAN	0	1000	(1000)	0	(100)	100
17	Order Totals			0	1000	(1000)	0	(100)	100

Before

And working on that makes a new sheet called “Output” that then looks like this:

Using Excel 2007

-	A	B	C	D	E	F	G	H	I
1	PO#	Item	Description	Per Deb	PerG	Variance	Per Deb	Per G	Variance
2	1000022	164B1822G001		0	480	-480	0	480	-480
3	1000022	123A1822H001		2520	480	2040	0	480	-480
4	1000070	1H4Y1387J004	ROD ANODE-50G	0	840	-840	0	-40	40
5	1000139	194J1734K002	BLADE FAN	0	1000	-1000	0	-100	100

Output

.....


Code in next post Post #5

Alan.

Code for post #4

Please Login or Register  to view this content.

Hi

Originally Posted by lengwer

This works exactly as I needed. ...... The way you have the code written I can use just one command button and it is completed on a separate tab. Thank you!!

....

. Yous welcome, thanks for the feedback

Originally Posted by lengwer

.... I went through a longer process. I had three separate command buttons and each preformed an action to set the file up as needed. ......

Below is how I had mine written:

....

. Thanks for feeding back and sharing. It is always good to see alternative solutions to the same problem.

Alan