Macro Cut/Paste Delete based on value of a cell

l8tnite

New Member
Joined
Apr 24, 2012
Messages
2
I'm in need of a macro that will cut and paste contents of a cell based on the value of another cell. More specifically, this macro needs to evaluate each cell in column B for a data set of 26000 rows, and every time it finds the text: "Item Totals:" it should copy the value that is in the cell below it and paste it in column A in the cell below that for as many times until it finds another "Item Totals:" Sounds confusing, but hopefully the illustration below can clarify that. Many thanks for all your help!

Example:

A B
1 Items Totals:
2 5
3 Blank
4 Blank
5 Blank
5 Item Totals:
6 3
7 Blank
8 Blank

Should look like this when done:

A B
1 Items Totals:
2 5
3 5 Blank
4 5 Blank
5 5
6 Item Totals:
7 3 Blank
8 3
 
Untested, but try this on a copy of your data and see if it works
Code:
Sub test()
    Dim a
    For Each a In Range("B1", Cells(Rows.Count, 1).End(xlUp)(1, 2)) _
            .SpecialCells(xlCellTypeBlanks).Areas
        a.Offset(0, -1).Value = a(0, 1).Value
    Next
End Sub
 
Upvote 0
Thank You for your help! The macro gave me a run time error. It didn't like this line: a.Offset(0, -1).Value = a(0, 1).Value

I was trying to attach a sample file but I'm not sure how. The formatting of my example is off. Column A in the original data is blank. The numbers 1-8 were meant to identify row numbers.

In the final output, column A should have a value for rows 3, 4, 5, 7, 8. The rest of the rows in column A should be blank.

Thank You!
 
Upvote 0
try
Code:
Sub test()
    Dim a
    For Each a In Range("B1", Cells(Rows.Count, 2).End(xlUp)) _
            .SpecialCells(xlCellTypeBlanks).Areas
        a.Offset(0, -1).Value = a(0, 1).Value
    Next
End Sub
 
Upvote 0

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