To copy, move, and then delete old row of data left.

[Cyberpawz]

I have a code I have been working on, I can get the first two parts working, but I can't get the final bit working... the delete old code.

Option Explicit

Sub MoveColumns()

    Dim cel As Range

    With ActiveSheet
        For Each cel In Intersect(.UsedRange, .[B:N]).SpecialCells(xlCellTypeBlanks).Cells
            cel = cel.Offset(0, 1)
        Next
    End With
End Sub

Attached is the sheet. The script is meant to run on BackOrder.

If you see it is meant to run specifically in columns B through N. It copies everything over to the left, but it doesn't delete the old column's data... that is the part I need to finish.

That is the part I can't figure out, if anyone who is more savvy than me can please, it be appreciated.

Just a heads up, the code is wrong in the page, I corrected it on the script here, so please copy and paste, it will make more sense then.

[111StepsAhead]

In simple English, what is the worksheet supposed to do? Explain as much as you can if you don't mind.

Thanks,

[Cyberpawz]

In simple English, what is the worksheet supposed to do? Explain as much as you can if you don't mind.

Thanks,

I already have.... but I'll repeat if I have to.

The script I have written copies all columns not directly next to each other over to the first left empty column it finds... what I want it to do is that once it has copied the columns over to delete the original so it isn't left there after the script has run.

That is as simple as I can get. Also the script has to be simple and straight forward like mine, because this is going into a much larger script, and must be self explanatory 3 months or 6 years down the road by just looking at it.

[Kelshaer]

Use this:

Sub MoveColumns()
    Dim oSH As Worksheet
    Dim r As Long
    Set oSH = ThisWorkbook.Worksheets("BackOrder")
    r = oSH.UsedRange.Rows.Count
    oSH.Range("B5:N" & r).Cut Destination:=Range("A5:M" & r)
End Sub

[Cyberpawz]

Use this:

Sub MoveColumns()
    Dim oSH As Worksheet
    Dim r As Long
    Set oSH = ThisWorkbook.Worksheets("BackOrder")
    r = oSH.UsedRange.Rows.Count
    oSH.Range("B5:N" & r).Cut Destination:=Range("A5:M" & r)
End Sub

Ummm, I hate to say it, but it didn't even come close to do what I wanted... It just moved everything over left, the format has to stay the same. B has to be the first row, that being said, it didn't delete any of the copied rows either.

[Kelshaer]

Sorry Cyberpawz but nothing in your original post says that you want to move or delete rows as well.

[Cyberpawz]

Sorry Cyberpawz but nothing in your original post says that you want to move or delete rows as well.

Actually I said columns, not rows, I can delete columns without issues... I thought I was quite clear.

If you see it is meant to run specifically in columns B through N. It copies everything over to the left, but it doesn't delete the old column's data... that is the part I need to finish.

That being said, I am still stuck.

[xladept]

Try this - cel = cel.Offset(0, 1):cel.Offset(0, 1)="" inserted as below

Option Explicit

Sub MoveColumns()

    Dim cel As Range

    With ActiveSheet
        For Each cel In Intersect(.UsedRange, .[B:N]).SpecialCells(xlCellTypeBlanks).Cells
            cel = cel.Offset(0, 1):cel.Offset(0, 1)=""
        Next
    End With
End Sub

[royUK]

SpecialCells will cause an error if there are no cells containing the criteria, in this case blanks. This method should always be used with an error statement to avoid issues.