Thread: Macro to move text

Perhaps like this?

Code:

With ActiveCell
    .Offset(-1).Value = .Offset(-1).Value & " " & .Value
    .EntireRow.Delete
End With

Hi StephenR,

That works perfectly!
I appreciate you taking the time to help me.

Many thanks,
Fredder

Hello,

I need to take this process one step further, and was wondering if anyone knew how to make this happen. I am using the script below and it works great. However, I have a table with thousands of rows of data, and doing this manually for each row just is not efficient. So here is what I am trying to do:

Same as below, except I need to be able to make the macro run automatically in a given worksheet by detecting the rows that are blank in column A and then run this macro accordingly in column B of that row which has been determined to be blank (as you can see, the import often cust the product description on to two rows instead of one, but I need the data all in one row to include the FULL description, which is often cut off).

I have attached a sample spreadsheet to show the BEFORE and AFTER of what I am hoping this macro can accomplish at the push of one button.

Would be happy to provide further clarification & details as needed. Many many thanks in advance for any help you can provide.

Cheers,
Fredder

Try this.

Code:

Sub x()

With Range("B3", Range("B" & Rows.Count).End(xlUp)).Offset(, -1).SpecialCells(xlCellTypeBlanks)
    .Offset(-1, 1).Value = .Offset(-1, 1).Value & " " & .Offset(, 1).Value
    .EntireRow.Delete
End With

End Sub

Hi StephenR,

Thanks so much for your reply. The macro seems to be doing something funny whereby the descriptions are duplicating themselves and not lining up properly with the part number.

I don't think I did the best job explaining myself so its probably my fault. Let me try again in simplest terms (please refer to revise file attached):

1 - the data will always start in A1 and be 3 colums wide, with col 1 being part number, col 2 being description, and col 3 being cost (no column titles will be included as per my previous file)

2 - i need the macro to scan the worksheet and do the following to every row that is blank in column A:

3 - using the attached file as an example, the macro would scan and the first empty cell in column A it would notice was A2. the macro would say move one cell to the right, to B2, take the data from B2 and add it to the data in the cell above, A2 (after adding a space to the end of the existing data in A2). Finally it would delete the now empty row B.

4 - the macro would then carry on and do this for all remaining empty cells in column A of the worksheet

...hope that helps! Again, your assistance is VERY VERY much appreciated.

Thanks,
Fredder

Please let me know how I can better improve my chances of receiving a solution - Is my issue clearly defined?

Thanks for your kind assistance.

My mistake Fredder, you explained your question perfectly well. Try this:

Code:

Sub x()

Dim rng As Range

With Range("B1", Range("B" & Rows.Count).End(xlUp)).Offset(, -1).SpecialCells(xlCellTypeBlanks)
    For Each rng In .Cells
        rng.Offset(-1, 1).Value = rng.Offset(-1, 1).Value & " " & rng.Offset(, 1).Value
    Next rng
    .EntireRow.Delete
End With

End Sub

AWESOME!!! Worked like a charm

Thanks again for your expertise, StephenR