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
 
Sort by date Sort by votes
Upload a copy of your workbook to www.box.com, mark it for sharing and then paste the link it provides in this thread.
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Your cells aren't blank, they have a ' (character 32) in them.
 
Last edited:
Upvote 0
Run the macro below


Code: 
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
MARK858 said:
Are you sure that your cells are truly blank and not formulas returning "" or downloaded data with non printing characters?
Click to expand...

that you actually checked.
 
Last edited:
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,808
Messages
6,181,073
Members
453,020
Latest member
mattg2448

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