Move Cell Value within the same Column Skipping Random Blanks Cells in between

[EvelynCarly]

Hi,

It might be very easy for your excel experts however I am stuck...

Any ideas on how to move cell value in the subtotal column to the corresponding row and skip the blank cells in between on Column H.

1. The current subtotal amount$ is on row below the breakdown on each items on the purchase order received.

2. I need the subtotal amount$ to be on the same rows that has the PO# and Vendor info.


Below is an image of the workbook.

Thank you in advance.. Feel free to let me know with any question. I can update the workbook

 

[BobUmlas]

Sub MoveIt()
i = Range("H1048576").End(xlUp).Row
j = Range("B1048576").End(xlUp).Row
1:
Cells(i, 8).Cut Cells(j, 8)
If j < 3 Then Exit Sub
i = j - 1
j = Cells(j - 1, 2).End(xlUp).Row
GoTo 1
End Sub

 

[Fazza]

I'm assuming the destination cells start blank. Select the whole range with subtotals data & blanks, then select blank cells only (CTR-G, ALT-S, k, return), then enter = and down arrow and CTRL-ENTER to load to all cells. This fills those blank cells with the value from the cell below it. Now paste special values the entire range of subtotals data. Apply the auto-filter to column G for everything except "Status", and delete the visible cells in column H. Then show all on the column G filter. OK?

 

[EvelynCarly]

Sub MoveIt()
i = Range("H1048576").End(xlUp).Row
j = Range("B1048576").End(xlUp).Row
1:
Cells(i, 8).Cut Cells(j, 8)
If j < 3 Then Exit Sub
i = j - 1
j = Cells(j - 1, 2).End(xlUp).Row
GoTo 1
End Sub

Awesome, thank you so much...……..

 

[EvelynCarly]

I'm assuming the destination cells start blank. Select the whole range with subtotals data & blanks, then select blank cells only (CTR-G, ALT-S, k, return), then enter = and down arrow and CTRL-ENTER to load to all cells. This fills those blank cells with the value from the cell below it. Now paste special values the entire range of subtotals data. Apply the auto-filter to column G for everything except "Status", and delete the visible cells in column H. Then show all on the column G filter. OK?

OMG, this works wonderfully. Thank you so much for your help!!!!!!

 

[Fazza]

you're welcome, Evelyn. all the best, Fazza

 

[EvelynCarly]

you're welcome, Evelyn. all the best, Fazza

You saved my day literally.. my report has 700,000 rows of data.

I am ready to submit to the board......

 

[Fazza]

with 700,000 rows of data, you might prefer a faster approach. below is some VBA
be sure to back up the file before testing

Sub anotherway()

    Dim i As Long
    Dim lLastRow As Long
    Dim cSubtotal As Currency
    Dim ar As Variant
    
    lLastRow = Cells(Rows.Count, "H").End(xlUp).Row
    With Range("H1:H" & lLastRow)
        ar = .Value2
        For i = lLastRow To 2 Step -1
            If Len(ar(i, 1)) > 0 Then
                cSubtotal = ar(i, 1)
                ar(i, 1) = vbNullString
            End If
            If Len(ar(i - 1, 1)) > 0 Then ar(i, 1) = cSubtotal
        Next i
        .Value2 = ar
    End With
End Sub

 

[EvelynCarly]

with 700,000 rows of data, you might prefer a faster approach. below is some VBA
be sure to back up the file before testing

Sub anotherway()

    Dim i As Long
    Dim lLastRow As Long
    Dim cSubtotal As Currency
    Dim ar As Variant
   
    lLastRow = Cells(Rows.Count, "H").End(xlUp).Row
    With Range("H1:H" & lLastRow)
        ar = .Value2
        For i = lLastRow To 2 Step -1
            If Len(ar(i, 1)) > 0 Then
                cSubtotal = ar(i, 1)
                ar(i, 1) = vbNullString
            End If
            If Len(ar(i - 1, 1)) > 0 Then ar(i, 1) = cSubtotal
        Next i
        .Value2 = ar
    End With
End Sub

Thanks a lot. I am very new to VBA coding. Allow me to test it out and will let you kown.

Cheers !