Why are my Excel Macros failing every time whenever they attempt to copy or paste a range?

user_14367085 0
Viewed 44

I have tried to do this a thousand different ways with no success. I need a Macro that will comb down a long list of products, copy the line below a product to be inline with it, and then delete the row it copied from.

I haven't reached the "delete" stage of the Macro, because the Macro never makes it past the Range().Copy. I have tried this a number of different ways, including Selecting the range first and specifying the workbook and worksheet (even though there is only one sheet, and I'm only copying within the sheet). I've even tried just recording a Macro, and then playing the Macro back, to see why it works - but it didn't work!

My spreadsheet looks something like:

SKU Name A B C D E F
123abc Pet Rock 0 1 0
0.00 123.45 0.00
456efg Birthday Suit 2 0 1
913.56 0 456.78

Formatted, it would look like this:

SKU Name A B C D E F
123abc Pet Rock 0 1 0 0.00 123.45 0.00
456efg Birthday Suit 2 0 1 913.56 0 456.78

And some code:

Sub Format()
    For Each r In Selection.Rows
        If Cells(r, 1).Value = "" Then
            Cells(r, 3).Resize(1, 3).Copy Cells(r, 6) 'have tried this
            Range("C" & r.Row & ":E" & r.Row).Copy Range("F" & r - 1) 'and something like this
            
            'some code to delete here
        End If
        Next r
End Sub

I recognise the code might be a little clunky (will clean up once it actually works). Also, other parts might not work that's not my concern, I just need to know why it fails whenever I attempt to copy a range. Even if that's defined by a recorded Macro.

I don't know if it matters, but I'm working on a Mac. I've been at this for a while, getting to my wits-end. Please help.

spreadsheet vba excel
1 Answers

Move Data: Push Up Right

  • Loops through the rows of a selection and when the cell in the first column is blank, two cells to the right of it, if selects 3 consecutive same row cells and copies them 3 cells to the right in the above column and deletes the row.
  • For example, if the blank cell is A5, it copies C5:E5 to F4:H4 and deletes row 5.
  • Note that it is enough to select the first column.
Option Explicit

Sub PushUpRight()
    
    Dim r As Long
    With Selection
        For r = .Rows.Count To 2 Step -1
            If .Cells(r, 1).Value = "" Then
                With .Cells(r, 1).Offset(0, 2).Resize(1, 3)
                    .Copy .Offset(-1, 3)
                    .EntireRow.Delete
                End With
            End If
        Next r
    End With

End Sub
user_98140690
Related