Help with a VBA macro to copy cell from row across and down in front of cells below

LazerGold

New Member
Joined
Nov 12, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

Long time reader, first time poster here! Have been struggling to work out the following, not having much success. I have multiple worksheets with hundreds of rows that have been categorised with section headers at various rows, similar to first sample below.

What I am trying to achieve is to copy these "categories" into the first column in front of the rows until it gets to the next category then copy that category in front of the next lot of rows so tha ti turns out like the second example. I then just plan to delete these original rows with the category names using a filter for blank rows in in column C.
Any help would be greatly appreciated!

Trying to go from this....
Book1
ABCDEFGHI
1GroupOne
21/06/2019EastJonesPencil951.99189.05
35/22/2019WestThompsonPencil321.9963.68
46/08/2019EastJonesBinder608.99539.4
56/25/2019CentralMorganPencil904.99449.1
6GroupTwo
77/12/2019EastHowardBinder291.9957.71
87/29/2019EastParentBinder8119.991,619.19
98/15/2019EastJonesPencil354.99174.65
109/01/2019CentralSmithDesk2125250
119/18/2019EastJonesPen Set1615.99255.84
1210/05/2019CentralMorganBinder288.99251.72
1310/22/2019EastJonesPen648.99575.36
14GroupThree
1511/08/2019EastParentPen1519.99299.85
1611/25/2019CentralKivellPen Set964.99479.04
1712/12/2019CentralSmithPencil671.2986.43
1812/29/2019EastParentPen Set7415.991,183.26
191/15/2020CentralGillBinder468.99413.54
202/01/2020CentralSmithBinder87151,305.00
21
22
Sheet1

to this by using a VBA macro
Book1
ABCDEFGH
1GroupOne
2GroupOne1/06/2019EastJonesPencil951.99189.05
3GroupOne5/22/2019WestThompsonPencil321.9963.68
4GroupOne6/08/2019EastJonesBinder608.99539.4
5GroupOne6/25/2019CentralMorganPencil904.99449.1
6GroupTwo
7GroupTwo7/12/2019EastHowardBinder291.9957.71
8GroupTwo7/29/2019EastParentBinder8119.991,619.19
9GroupTwo8/15/2019EastJonesPencil354.99174.65
10GroupTwo9/01/2019CentralSmithDesk2125250
11GroupTwo9/18/2019EastJonesPen Set1615.99255.84
12GroupTwo10/05/2019CentralMorganBinder288.99251.72
13GroupTwo10/22/2019EastJonesPen648.99575.36
14GroupThree
15GroupThree11/08/2019EastParentPen1519.99299.85
16GroupThree11/25/2019CentralKivellPen Set964.99479.04
17GroupThree12/12/2019CentralSmithPencil671.2986.43
18GroupThree12/29/2019EastParentPen Set7415.991,183.26
19GroupThree1/15/2020CentralGillBinder468.99413.54
20GroupThree2/01/2020CentralSmithBinder87151,305.00
21
Sheet1
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,591
Office Version
  1. 2013
Platform
  1. Windows
Try this:

VBA Code:
Sub Copy_Group()
'Modified  11/12/2020  11:36:24 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row

    For i = 1 To Lastrow
        If Cells(i, 3).Value = "" Then ans = Cells(i, 2).Value: i = i + 1
        Cells(i, 1).Value = ans
    Next

Application.ScreenUpdating = True
End Sub
 
Solution

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,591
Office Version
  1. 2013
Platform
  1. Windows
My script looks down column C for a empty cell. If it finds a empty cell it looks for the group name in column B
 

LazerGold

New Member
Joined
Nov 12, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thanks so much. That's the approach I tried but just didn't quite get there.

I'll give this a go ASAP and report back :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,127
Messages
5,640,277
Members
417,133
Latest member
caaronh85

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
Top