Copy and paste values from above rows in case of blank

J

jatinjoshi

New Member
Joined
Feb 5, 2014
Messages
34
I want to create macro to autofill values from rows containing values from the above rows.

Sample

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

Final Result

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

My columns are dynamic.

Thanks in advance
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
My columns are dynamic.
Click to expand...

Just checking... do you mean the number of columns is dynamic or the number of rows is dynamic?
 
Upvote 0
Also why are certain columns not filled down? Is this deliberate? If yes list the columns not to be filled down.
 
Upvote 0
Sorry for my mistakes.
Both Columns and Rows are dynamic and all the Blank rows has to be filled from values above.
 
Upvote 0
Assuming your data starts in A1. try the below...

Code: 
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
 
Upvote 0
It is not working. I have tried A1 and A2 also. My data has headers and it will start in A2
i1O1MU


5xu3gk.jpg
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to copy and paste the next row VBA Help.
Replies
8
Views
546
James006
James006
D
  • Question Question
Find the matching details from 2 workbooks compile it in a sheet and then summarize it and put it in different sheet.
Replies
0
Views
395
deba2020
D
P
  • Question Question
How to copy and paste the next row VBA Help.
Replies
4
Views
472
punnipah
P
H
  • Question Question
Insert Blank row and sum the values
Replies
4
Views
606
JLGWhiz
JLGWhiz
Z
  • Question Question
Need help trying to copy a sheet paste to another and make it work the way i need. I recorded what im trying to do and i can explain more.
Replies
8
Views
608
Michael M
Michael M

Forum statistics

Threads
1,224,802
Messages
6,181,054
Members
453,014
Latest member
Chris258

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top