Need Help to Combine cells and then remove unneeded Rows

[BILLCRW]

If there is anyone that could help me on this I would greatly appreciate it.

On the sample below I would like a macro that will combine the data into C2 when there is no info in A and then remove the rows after they have been combined

The tricky part is the amount of rows can very. I hope I am clear but if not please let me know and I will try and explain it better

1	4	C3,C12,C16,C19	CAP ALUM 100UF 35V 20% SMD	PANASONIC	ECE-V1VA101P
2	56	C11,C13,C18,C57,C59,	CAP CER 0.1UF 10V 10% X7R 0603	KEMET	C0603C104K8RACTU
		C61,C62,C63,C64,C65,
		C66,C67,C68,C69,C70,
		C71,C72,C73,C74,C75,
		C76,C77,C80,C81,C82,
		C83,C84,C85,C86,C87,
		C88,C89,C92,C93,C94,
		C95,C96,C98,C99,C100,
		C101,C102,C103,C104,
		C105,C106,C107,C108,
		C109,C110,C11,C112,
		C113,C114,C115,C116
3	1	C34	CAP CER 1UF 35V 10% X5R 0603	MURATA	GMK107BJ105KA-T
4	1	C47	CAP CER 10000PF 50V 10% X7R 0603	KEMET	C0603C103K5RACTU
5	6	D9,D11,D13,D17,D18,D22	DIODE SCHOTTKY 40V 1A SOT23-3	DIODES	ZHCS1000TA
6	8	D23,D24,D25,D26,D27,	LED TBD COLOR
		D28,D29,D30
7	1	F1	FUSE PTC 1.10A 30V FST-TRIP SMD	SCHURTER	PFSM.100.2

<tbody>
</tbody>

 

[Momentman]

Please provide Before and after scenarios so we know how exactly the output should look like

 

[BILLCRW]

Sure,

Here is before

1	4	C3,C12,C16,C19	CAP ALUM 100UF 35V 20% SMD	PANASONIC	ECE-V1VA101P
2	56	C11,C13,C18,C57,C59,	CAP CER 0.1UF 10V 10% X7R 0603	KEMET	C0603C104K8RACTU
		C61,C62,C63,C64,C65,
		C66,C67,C68,C69,C70,
		C71,C72,C73,C74,C75,
		C76,C77,C80,C81,C82,
		C83,C84,C85,C86,C87,
		C88,C89,C92,C93,C94,
		C95,C96,C98,C99,C100,
		C101,C102,C103,C104,
		C105,C106,C107,C108,
		C109,C110,C11,C112,
		C113,C114,C115,C116
3	1	C34	CAP CER 1UF 35V 10% X5R 0603	MURATA	GMK107BJ105KA-T
4	1	C47	CAP CER 10000PF 50V 10% X7R 0603	KEMET	C0603C103K5RACTU
5	6	D9,D11,D13,D17,D18,D22	DIODE SCHOTTKY 40V 1A SOT23-3	DIODES	ZHCS1000TA
6	8	D23,D24,D25,D26,D27,	LED TBD COLOR
		D28,D29,D30
7	1	F1	FUSE PTC 1.10A 30V FST-TRIP SMD	SCHURTER	PFSM.100.2
8	3	HS3,HS4,HS5	Heat Sink (TO220 Hat)
9	1	J26	25 Pin D Connector		A32110
10	1	J3	Conn Unshrouded Header HDR 6 POS 2.54mm	SAMTEC	TSW-106-07-S-S
11	2	J29,J30	BANANA JACK		CT3151-2
12	2	J49,J51	100 Pin MDM Connector		M83513/12-H
13	1	J52	25 Pin MDM Connector		M83513/10-D
14	1	L1	INDUCTOR POWER 10UH 5.3A SMD	BOURNS	PM5022-100M-RC
15	3	LA4,LA5,LA6	CONN RCPT 38POS .025 VERT SMD	TE CONNECT	767054-1
16	4	R16,R18,R24,R26	RES 1.2K OHM 1/10W 1% 0603 SMD	PANASONIC	ERJ-3EKF1201V
17	3	R17,R25,R39	TRIMMER 100 OHM 0.125W SMD	COPAL	ST32ETA101
18	20	R38,R161,R165,R166,R167,	RES 4.7K OHM 1/10W 1% 0603 SMD	PANASONIC	ERJ-3EKF4701V

<tbody>
</tbody>

And here is after

1	4	C3,C12,C16,C19	CAP ALUM 100UF 35V 20% SMD	PANASONIC	ECE-V1VA101P
2	56	C11,C13,C18,C57,C59, C61,C62,C63,C64,C65, C66,C67,C68,C69,C70, C71,C72,C73,C74,C75, C76,C77,C80,C81,C82, C83,C84,C85,C86,C87, C88,C89,C92,C93,C94, C95,C96,C98,C99,C100, C101,C102,C103,C104, C105,C106,C107,C108, C109,C110,C11,C112, C113,C114,C115,C116	CAP CER 0.1UF 10V 10% X7R 0603	KEMET	C0603C104K8RACTU
3	1	C34	CAP CER 1UF 35V 10% X5R 0603	MURATA	GMK107BJ105KA-T
4	1	C47	CAP CER 10000PF 50V 10% X7R 0603	KEMET	C0603C103K5RACTU
5	6	D9,D11,D13,D17,D18,D22	DIODE SCHOTTKY 40V 1A SOT23-3	DIODES	ZHCS1000TA
6	8	D23,D24,D25,D26,D27,D28,D29,D30	LED TBD COLOR
7	1	F1	FUSE PTC 1.10A 30V FST-TRIP SMD	SCHURTER	PFSM.100.2
8	3	HS3,HS4,HS5	Heat Sink (TO220 Hat)
9	1	J26	25 Pin D Connector		A32110
10	1	J3	Conn Unshrouded Header HDR 6 POS 2.54mm	SAMTEC	TSW-106-07-S-S
11	2	J29,J30	BANANA JACK		CT3151-2
12	2	J49,J51	100 Pin MDM Connector		M83513/12-H
13	1	J52	25 Pin MDM Connector		M83513/10-D
14	1	L1	INDUCTOR POWER 10UH 5.3A SMD	BOURNS	PM5022-100M-RC
15	3	LA4,LA5,LA6	CONN RCPT 38POS .025 VERT SMD	TE CONNECT	767054-1
16	4	R16,R18,R24,R26	RES 1.2K OHM 1/10W 1% 0603 SMD	PANASONIC	ERJ-3EKF1201V
17	3	R17,R25,R39	TRIMMER 100 OHM 0.125W SMD	COPAL	ST32ETA101
18	20	R38,R161,R165,R166,R167, R169,R170,R171,R172, R173,R174,R175,R176, R177,R178,R179,R180, R181,R182,R183	RES 4.7K OHM 1/10W 1% 0603 SMD	PANASONIC	ERJ-3EKF4701V
19	1	R168	RES 300 OHM 1/10W 1% 0603 SMD	PANASONIC	ERJ-3EKF3000V

<tbody>
</tbody>

Thanks for your help.

Bill

 

[Rick Rothstein]

Give this macro a try...

Sub CombineAndDelete()
  Dim LastRow As Long, B As Range, Blanks As Range
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Set Blanks = Columns("A").SpecialCells(xlBlanks)
  For Each B In Blanks.Areas
    With B.Offset(-1, 2).Resize(B.Rows.Count + 1)
      .Cells(1).Value = Join(Application.Transpose(.Cells), "")
    End With
  Next
  Blanks.EntireRow.Delete
End Sub

 

[BILLCRW]

Give this macro a try...

Sub CombineAndDelete()
  Dim LastRow As Long, B As Range, Blanks As Range
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Set Blanks = Columns("A").SpecialCells(xlBlanks)
  For Each B In Blanks.Areas
    With B.Offset(-1, 2).Resize(B.Rows.Count + 1)
      .Cells(1).Value = Join(Application.Transpose(.Cells), "")
    End With
  Next
  Blanks.EntireRow.Delete
End Sub

Worked Perfect!!!! Thank you very very much.

Bill