Carry Down Multiple Rows of Data to End of Specific Section

[jski21]

Good morning Mr. Excel Team,

I have a rather lengthy spreadsheet downloaded from a program that forsakes any and all friendliness to a workable Excel format. I've got some code to deal with most of the issues but having a bit of trouble with this one. Here's a snapshot:

PR 05 - Drawdown Report 10.1.21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Program Year	Project	Project #	Activity Name	Voucher	Line Item	Voucher Status	LOCCS Send Date	Start Year	ProjectCode	Type	Drawn Amount
2	2015	A	13613	ABC	5879572	1	Completed	12/21/2015	2015	B15MC390004	EN	$419,000.00
3	2015
4	2015
5	2015
6	2015	B	13627	DEF	5895204	1	Completed	2/10/2016	2015	B15MC390004	PI	$170,384.50
7	2015	B	13627	DEF	5986234	1	Completed	11/23/2016	2015	B15MC390004	EN	$313,014.25
8	2015	B	13627	DEF	6012745	3	Completed	2/17/2017	2015	B15MC390004	EN	$1,200.00
9	2015	B	13627	DEF	6014555	2	Completed	2/22/2017	2015	B15MC390004	EN	$62,399.50
10	2015
11	2015
12	2015
13	2015
14	2015	G	13604	HIJ	5879561	6	Completed	12/21/2015	2015	B15MC390004	EN	$326,290.00
15	2015				5882226	1	Completed	12/30/2015	2015	B15MC390004	EN	$98,338.00
16	2015				5882229	1	Completed	12/30/2015	2015	B15MC390004	EN	$486,150.00
17	2015				5903960	2	Completed	3/8/2016	2015	B15MC390004	EN	$2,152.01
18	2015				5986234	4	Completed	11/23/2016	2015	B15MC390004	EN	$115,917.99
19	2015				6130991	1	Completed	3/8/2018	2015	B15MC390004	EN	$33,465.00
20	2015				6165788	1	Completed	6/25/2018	2015	B15MC390004	EN	$71,204.00
21	2015				6179676	1	Completed	8/9/2018	2015	B15MC390004	EN	$119,000.00
22	2015				6470735	2	Completed	3/11/2021	2015	B15MC390004	EN	$600.00
PR 05 - Drawdown Report by (2

Essentially, I'm seeking to fill in the rows below in Columns B, C, and D with the copied data from the initial row of the line item. An example of this completed exercise is in Row 6 - Row 9. The initial row is Row 6 and that information is copied down all the way to the end of that section, which is E9.

An uncompleted section resides in Row 14. The data in B14, C14, and D14 would need to be copied down to the end of its section, which would be E22. And so on, and so on, until the end of the mess in the very last row which will be dynamic as additional data is added.

Notice some of the rows are single entries and that is ok. It's the multiple entry rows (2 or more) that give me heartburn. The goal is to eventually have a flat file or table I can work with.

I'll need to eliminate all the spaces once all the data is filled. Any simple method to do that would be appreciated as well. I have some code to do that but I believe it's rather awkward as it takes a bit of time to run.


Thanks in advance for any assistance. I always come away from this forum with a bit more knowledge about Excel and VBA.


Regards,

jski

 

[Fluff]

Code removed, I misunderstood the question.

 

[Fluff]

How about

Sub jski()
   Dim Rng As Range
   
   For Each Rng In Range("G2", Range("G" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      On Error Resume Next
      With Rng.Offset(, -5).Resize(, 3)
         .SpecialCells(xlBlanks).Formula = "=r[-1]c"
         .Value = .Value
      End With
      On Error GoTo 0
   Next Rng
End Sub

 

[jski21]

Wow...that worked swiftly and did the job. Thanks Fluff! A few questions if I may:

1. What is .SpecialCells(xlConstants.Areas?
2. What is "=r[-1]c"?
3. Why G2 instead of E2?

 

[Fluff]

1) The special cells finds all cells that are constants & each group of contiguous cells is an area.
2) it's R1C1 notation & in this case means the row above.
3) No particular reason.

 

[jski21]

Got it. Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.