VBA to split data

[Akira181]

I have a excel bill of materials for a machine extracted from my works database that's over 31k rows long and quite messy in presentation. There's blank rows in between, some cells are merged, some cells are blank but the row contains information needed, etc.

The information is divided into assemblies, then each assembly split into levels. All assemblies are "Level 1" as indicated in column A. I would like a macro that is able to copy all rows from the first "1" in column A up to (but not including) the next "1" in Column A and paste it into a new worksheet. I would like to do this for each assembly and also remove all the blank rows inbetween so it's easier to read.

I was trying to follow this thread, VBA copy rows between two specific values, but haven't been able to get it to work for my needs.. Can someone help a total VBA novice?

BOM.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
19	Level		Balloon					Component Item No.			Quantity Per Order		Quantity Per Unit		U/M				Extended Description		Drawing #			Revision				Drawing Size			Item Group
20
21
22
23		1		01					xxxxxx			1.00		1.00		EA				Project Number 123 - Assembly ABCDE													Parts
24
25
26		2		01A					XXXXXXXXX			1.00		1.00		EA				AAA		123456							D				Parts
27																										A
28
29		3		200					XXXXXXXXX			3.00		3.00		EA				BBB		123456							D				Parts
30																										A
31
32
33		4		1					XXXXXXXXX			3.00		1.00		ea				CCC		123456							D				Parts
34																										A
35
36
37
38		7		01					XXXXXXXXX			6.00		1.00		EA				DDD		123456											Parts
39
40
41
42																				DDD1
43
44
45									Manufacturer:			Vendor		MFG CODE						DDD2
46
47		7		01					XXXXXXXXX			0.00		0.00		lf				EEE		123456											Raw Materials
48
49
50		1		01					xxxxxx			1.00		1.00		EA				Project Number 123 - Assembly FGH													Parts
51
52
53		2		004					10441702			6.00		2.00		EA				FFF		10579002											Parts
54
BOM (2)

So ideally, Rows 23/24 to 47/48 would be copied to a new worksheet and the blank rows and cell merges removed if possible (nice to have, can be ignored if it's difficult to do). Then Rows 50/51 to the last row prior to the next "1" gets copied to a different worksheet, and so on.

 

[duggie33]

Is there another way to export your bill of materials from the database? I unmerged the cells, deleted all the empty columns, shifted the headers to the right one column, deleted more empty columns, shifted the revisions up one row, and then deleted the empty rows to make it look like below...

Book1
	A	B	C	D	E	F	G	H	I	J	K
19	Level	Balloon	Component Item No.	Quantity Per Order	Quantity Per Unit	U/M	Extended Description	Drawing #	Revision	Drawing Size	Item Group
20	1	1	xxxxxx	1	1	EA	Project Number 123 - Assembly ABCDE				Parts
21	2	01A	XXXXXXXXX	1	1	EA	AAA	123456	A	D	Parts
22	3	200	XXXXXXXXX	3	3	EA	BBB	123456	A	D	Parts
23	4	1	XXXXXXXXX	3	1	ea	CCC	123456	A	D	Parts
24	7	1	XXXXXXXXX	6	1	EA	DDD	123456			Parts
25							DDD1
26			Manufacturer:	Vendor	MFG CODE		DDD2
27	7	1	XXXXXXXXX	0	0	lf	EEE	123456			Raw Materials
28	1	1	xxxxxx	1	1	EA	Project Number 123 - Assembly FGH				Parts
29	2	4	10441702	6	2	EA	FFF	10579002			Parts
Sheet2

On a larger scale, the lengthy part would be deleting rows. A macro could be written to do that pretty easily.

I am unsure why some cells have extra spaces (revision column), and I am not sure what is going on in rows 25 and 26. If you could get a cleaner export, the spreadsheet would be much easier to digest, and extracting the level 1 assemblies would be easier and cleaner.

Doug