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]

Upload a copy of your workbook to www.box.com, mark it for sharing and then paste the link it provides in this thread.

 

[jatinjoshi]

Link for the Excel file

[URL]https://app.box.com/s/rfxphfwt46hnq3069jcb6djkmnnekipe[/URL]

 

[MARK858]

Your cells aren't blank, they have a ' (character 32) in them.

 

[jatinjoshi]

It is generated by software. Can you make changes to macro that way

 

[MARK858]

Run the macro below

Sub Trimit()
    Dim myCell As Range, myRng As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set myRng = ActiveSheet.UsedRange
    
    With myRng
        .Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(21), Replacement:=Chr(32), LookAt:=xlPart

        .Replace What:=Chr(8), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(9), Replacement:=Chr(32), LookAt:=xlPart
    End With

    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                               myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0

    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Then run the macro from earlier.

In future when asked questions like

Are you sure that your cells are truly blank and not formulas returning "" or downloaded data with non printing characters?

that you actually checked.

 

[jatinjoshi]

Thanks.

I apologize for not mentioning it clearly.

Thanks again for your effor