Macro Cut/Paste Delete based on value of a cell

[l8tnite]

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

 

[trunten]

Untested, but try this on a copy of your data and see if it works

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

 

[l8tnite]

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!

 

[trunten]

try

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