auto fill blanks in multiple columns with formula or macro

[markman235]

Hi everyone,

I have a sheet with several hundred line items. Columns A - I are only populated at the start of each unique PO.

So if i have a PO with 5 line items, columns A - I would have the PO data in row 1, but 2 - 5 would be blank. Row 6 would be populated with the data from the next PO.

Is there an easy way with either a macro or formula to get these columns to copy data to the blank cells?

Thanks everyone,

Mark

 

[alansidman]

I can think of two ways to do this. One would be using VBA and the other would use Power Query/Get and Transform. If you can post some sample data showing what your worksheet would look like, I am sure that someone here (me included) can offer up a viable solution that will work for you.

 

[markman235]

Thanks Allen, I need all the help i can get.

This is the sheet i am working with.

Division	Number	ID	Number	Day Code	Time	Status	Date	Number	Number	UPC	Quantity	Quantity
755	17	2	11479	9	11:48	D	<st1:date ls="trans" month="12" day="2" year="2018">12/2/2018</st1:date>	68021	303	1001111050266	228	228
									304	1001111050268	350	350
									607	1001111050279	114	114
									2932	1001111050312	140	140
									2933	1001111050313	114	114
									2935	1001111050315	456	456
									2937	1001111050317	200	200
									57130	1001111050072	114	114
755	17	2	11497	9	11:48	D	<st1:date ls="trans" month="12" day="4" year="2018">12/4/2018</st1:date>	68470	303	1001111050266	342	342
									304	1001111050268	420	420
									2932	1001111050312	350	350
									2933	1001111050313	228	228
									2935	1001111050315	684	684
755	17	2	11498	9	11:48	D	<st1:date ls="trans" month="12" day="4" year="2018">12/4/2018</st1:date>	68471	344	1001111050323	70	70

<colgroup><col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" span="2"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody> </tbody>

 

[alansidman]

If you use Power Query, first convert your range of data to a table and name it Table3. Then.....

Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the following code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Number", Int64.Type}, {"ID", Int64.Type}, {"Number2", Int64.Type}, {"Day Code", Int64.Type}, {"Time", type number}, {"Status", type text}, {"Date", type datetime}, {"Number3", Int64.Type}, {"Number4", Int64.Type}, {"UPC", Int64.Type}, {"Quantity", Int64.Type}, {"Quantity5", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Division", "Number", "ID", "Number2", "Day Code", "Time", "Status", "Date", "Number3"})
in
    #"Filled Down"

 

[Fluff]

Or with a macro

Sub MyFillDown()
With Range("A2", Range("J" & Rows.Count).End(xlUp).Offset(, -1))
   .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   .Value = .Value
End With
End Sub

 

[markman235]

Thank you Alan!!

 

[markman235]

Thank you Fluff!!!

 

[Fluff]

You're welcome

 

[Declamatory]

If I understand it each blank cell in your range would equal the cell above. I don't think you need a macro for this although it is quicker I expect.

If you select your data then right click and press CTRL+G
Click on Special and tick 'Blanks' then press OK. All your blank cells in the range are selected.
Then press = then your up arrow
Press CTRL and ENTER.