Excel VBA Loop then Delete

[XfortunaX]

Hi,

This sounds simple, but I am stuck. I am looking for a simple loop that will count the number of rows down then start the process of moving B2:E2 to C1:F1 then B4:E4 to C3:F3 and so on until there are not anymore rows.

In the end, it will use the following to delete all rows that do not have information in B.

Sub MoveCellsUp()

    Dim lr As Long

    lr = Range("A1").End(xlDown).Row

    'Loop that will cut and paste the cells up and over a row then move down to the next set

    'Delete all empty Rows
    [B:B].SpecialCells(xlBlanks).EntireRow.Delete

    End Sub

Thank you,

JT

 

[Takae]

Hope this helps.
Please save your data before run it.

Sub MoveCellsUp()
    Dim lr As Long, i As Long
    lr = Range("A1").End(xlDown).Row
    'Loop that will cut and paste the cells up and over a row then move down to the next set
    For i = lr To 2 Step -2
        Range(cells(i, 2), cells(i, 6)).Cut cells(i - 1, 3)
        Rows(i).Delete
    Next
End Sub

 

[XfortunaX]

Takae,

Thank you for the response. I am sorry on my late response.

The original data dump looks like:


After formatting:


The loop gets the price below (or prices) below the description and moves it to the right of the description then deletes the rows until it hits the next description.

I cannot get the loop to grab all of the information. Sometimes the data is columns out.

Thanks!

John

 

[XfortunaX]

The original data image above is correct, the second image is erroneous. Should be deleted.