Question:  I have an Excel spreadsheet that contains an export of some raw data. However, the data does not continue on the same row, it drops to the next one. How can I build a macro that will look at the first entry "Word" in column A, and move it to a certain cell in the previous row.

For example:
If cell A? says "TimberWeight" - nothing needs to be done.
If cell A? says "Contract Information", the rest of the row (ie: cells A through H) needs to be moved to the previous row, cell M.
If cell A? says "Location", the rest of the row (ie: cells A through T) needs to be moved to the previous row, cell T.

Then the blank rows need to be deleted.

Answer:  Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In this spreadsheet, we've created a macro called CleanupData. You can run the macro by selecting Macro > Macros under the Tools menu. Then highlighting the macro called CleanupData and clicking on the Run button.

Once the macro has run, the spreadsheet will look as follows:

You can press Alt-F11 to view the VBA code.

Macro Code:

The macro code looks like this:

Sub CleanupData()

    Dim LRow As Integer

    LRow = 1

    'Move through records until an empty cell is found in column A
    While IsEmpty(Range("A" & CStr(LRow)).Value) = False

        'If cell A? displays "Contract Information" then move the row to
        'cell M of the previous row and then delete the empty row
        If Range("A" & CStr(LRow)).Value = "Contract Information" Then
            'Move the row
            Range("A" & LRow & ":H" & LRow).Select
            Selection.Cut
            Range("M" & LRow - 1).Select
            ActiveSheet.Paste

            'Delete the empty row
            Rows(LRow & ":" & LRow).Select
            Selection.Delete Shift:=xlUp

            'Decrement counter since row was deleted
            LRow = LRow - 1

        'If cell A? displays "Location" then move the row to
        'cell T of the previous row and then delete the empty row
        ElseIf Range("A" & CStr(LRow)).Value = "Location" Then
            'Move the row
            Range("A" & LRow & ":T" & LRow).Select
            Selection.Cut
            Range("T" & LRow - 1).Select
            ActiveSheet.Paste

            'Delete the empty row
            Rows(LRow & ":" & LRow).Select
            Selection.Delete Shift:=xlUp

            'Decrement counter since row was deleted
            LRow = LRow - 1
        End If

        LRow = LRow + 1
    Wend

End Sub