Copy and paste values from above rows in case of blank

[jatinjoshi]

I want to create macro to autofill values from rows containing values from the above rows.

Sample

Bill No.	Bill Date	Billed Party Name	GSTIN	Sale/Purchase Type	Company Name	led Party State Name	Item Name	Item MRP	Price	Qty	Free Quantity	Amount	Item Tax Category	HSN Code	BS1 Name	BS1(%)	BS1 Amount	BS2 Name	BS2(%)	BS2 Amount	BS3 Name	BS3(%)	BS3 Amount	BS4 Name	BS4(%)	BS4 Amount	BS5 Name	BS5(%)	BS5(Sub Total)
NGK/C/0001/18-19	02-08-2018	A K DRESSES	19BXVPD3024N1ZZ	L/GST-MULTITAX	Nagji	West Bengal (19)	B.S - KOOL - 6846 - 18/22		95.00	3.00	0	285.00	5	6103	CGST	2.50	17.25	SGST	2.50	17.25	Rounded Off (+)		0.50			0			0
							B.S - KOOL - 6858A - 28/32		135.00	3.00	0	405.00	5	6103		0.00	0.00			0.00			0.00			0			0
NGK/C/0002/18-19	12-08-2018	A.Fashion	18ADAPT5416C1ZR	L/GST-MULTITAX	Nagji	Assam (18)	BABA SUIT - DOTS - 1271 - MLXL		100.00	6.00	0	600.00	5	6111	CGST	2.50	38.00	SGST	2.50	38.00			0.00			0			0
							B-DENIM-Ftc Jeans-2939-32/40		460.00	2.00	0	920.00	5	6203		0.00	0.00			0.00			0.00			0			0

<colgroup><col><col><col><col span="2"><col><col span="2"><col span="5"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Final Result

Bill No.	Bill Date	Billed Party Name	GSTIN	Sale/Purchase Type	Company Name	led Party State Name	Item Name	Item MRP	Price	Qty	Free Quantity	Amount	Item Tax Category	HSN Code	BS1 Name	BS1(%)	BS1 Amount	BS2 Name	BS2(%)	BS2 Amount	BS3 Name	BS3(%)	BS3 Amount	BS4 Name	BS4(%)	BS4 Amount	BS5 Name	BS5(%)	BS5(Sub Total)
NGK/C/0001/18-19	02-08-2018	A K DRESSES	19BXVPD3024N1ZZ	L/GST-MULTITAX	Nagji	West Bengal (19)	B.S - KOOL - 6846 - 18/22		95.00	3.00	0	285.00	5	6103	CGST	2.50	17.25	SGST	2.50	17.25	Rounded Off (+)		0.50			0			0
NGK/C/0001/18-19	02-08-2018	A K DRESSES	19BXVPD3024N1ZZ	L/GST-MULTITAX	Nagji		B.S - KOOL - 6858A - 28/32		135.00	3.00	0	405.00	5	6103	CGST	2.50	17.25	SGST	2.50	17.25	Rounded Off (+)		0.50			0			0
NGK/C/0002/18-19	12-08-2018	A.Fashion	18ADAPT5416C1ZR	L/GST-MULTITAX	Nagji	Assam (18)	BABA SUIT - DOTS - 1271 - MLXL		100.00	6.00	0	600.00	5	6111	CGST	2.50	38.00	SGST	2.50	38.00			0.00			0			0
NGK/C/0002/18-19	12-08-2018	A.Fashion	18ADAPT5416C1ZR	L/GST-MULTITAX	Nagji		B-DENIM-Ftc Jeans-2939-32/40		460.00	2.00	0	920.00	5	6203	CGST	2.50	38.00	SGST	2.50	38.00			0.00			0			0

<colgroup><col><col><col><col span="2"><col><col span="2"><col span="5"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

My columns are dynamic.

Thanks in advance

 

[MARK858]

My columns are dynamic.

Just checking... do you mean the number of columns is dynamic or the number of rows is dynamic?

 

[MARK858]

Also why are certain columns not filled down? Is this deliberate? If yes list the columns not to be filled down.

 

[jatinjoshi]

Sorry for my mistakes.
Both Columns and Rows are dynamic and all the Blank rows has to be filled from values above.

 

[MARK858]

Assuming your data starts in A1. try the below...

Sub FillCell2()
    Dim lr As Long, lc As Long

    lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    With Range(Cells(3, "A"), Cells(lr, lc))
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(R[-1]C<>"""",R[-1]C,"""")"
        .Value = .Value
    End With
End Sub

 

[jatinjoshi]

It is not working. I have tried A1 and A2 also. My data has headers and it will start in A2

 

[MARK858]

Works fine at my end. Are you sure that your cells are truly blank and not formulas returning "" or downloaded data with non printing characters?
By the way I can see you had headers from the first post and the code allowed for that.

 

[jatinjoshi]

I have tried with both header on and then deleting header in both case it is not working and fills 43324. I am using excel 2016

 

[MARK858]

That number is a date either comment out the .Value line so it remains as a formula or format the date columns as dates.
Please don't remove the header row as I have already told you that the code allows for the header row... sigh

 

[jatinjoshi]

I have formatted it to date. it copies value to Column B only. I want to copy the values from above cell for every column if its blank.