Paste Range Returning Syntax Error For Variable Range

Question

I am trying to loop through a set of columns with a list of dates that vary in size and have numerous duplicates. I want to copy the column values from TSX-DeltaFind and paste them in TSX-CleanDate under the corresponding "Ticker" and to remove all duplicates once complete.

A) Why am I getting a syntax error on the paste line?
B) How can I delete all duplicates once these paste?

Sub CleanDate()
    Dim BottomRow As Long
    Dim BottomRow2 As Long
    Dim TopRow As Long
    Dim col As Integer
    Dim Ticker As String
    Dim RngY As Range
    Dim originalRng As Integer

    With ThisWorkbook.Worksheets("TSX-DeltaFind")
        col = 4
        TopRow = 6

        For col = 4 To 3 + (2 * 26) Step 2
            Ticker = .Cells(TopRow - 1, col - 2).Value
            BottomRow = .Cells(.Rows.Count, col).End(xlUp).row
            originalRng = BottomRow - TopRow

            Worksheets("TSX-DeltaFind").Range(.Cells(TopRow, col), .Cells(BottomRow, col)).Copy
            Worksheets("TSX-CleanDate").Activate

            With ThisWorkbook.Worksheets("TSX-CleanDate")
                Set RngY = Worksheets("TSX-CleanDate").Range("A3:XDF3").Find(Ticker, lookat:=xlPart)
                BottomRow2 = .Cells(.Rows.Count, RngY.Column).End(xlUp).row

                Worksheets("TSX-CleanDate").Range(BottomRow2 + 1, RngY.Column + 2:originalRng + 4,RngY.Column + 2 ).PasteValues
            End With

            Worksheets("TSX-DeltaFind").Activate
        Next
    End With
End Sub

If all those variables are numbers the paste destination amounts to something like `Range(2, 3:5,3 )` which is not valid syntax. I reckon you need `Cells`. — SJR, Oct 24 '18 at 15:02

OwlsSleeping · Answer 1 · 2018-10-24T15:17:49.823

1

For A) Your range argument should be something like range("a2:b2") rather than range(1,2:2,2)

You can use Cells to refer to them in the way you're trying. For example

Worksheets("TSX-CleanDate").Range(BottomRow2 + 1, RngY.Column + 2:originalRng + 4,RngY.Column + 2 ).PasteValues

Could be

Worksheets("TSX-CleanDate").Range(Cells(BottomRow2 + 1, RngY.Column + 2),Cells(originalRng + 4,RngY.Column + 2)).PasteSpecial (xlPasteValues)

B) For duplicates, use range.removeduplicates

edited Oct 24 '18 at 15:17

answered Oct 24 '18 at 15:04

OwlsSleeping

1,487
2
11
19

The new line you gave me returns an error "object doesnt support this property or method" – OMO Oct 24 '18 at 15:07
`Pastevalues` is not valid syntax. You should also qualify all the cells with the sheet. – SJR Oct 24 '18 at 15:14
Could you please explain? I'm pretty new to this...i changed the "pastevalues" to "paste" still no luck. – OMO Oct 24 '18 at 15:17
Added in correct paste method. You already used With ThisWorkbook.Worksheets("TSX-CleanDate"), why type it 4 times? – OwlsSleeping Oct 24 '18 at 15:19
I was just trying to see if the error had something to do with the page reference, it was more just a safety check. – OMO Oct 24 '18 at 15:21
@OwlsSleeping after pasting the data into the correct range, i'm trying to remove the duplicates. I'm not getting an error but the function simply doesnt do anything. I basically use the same .Range as with the PasteSpecial but i use .RemoveDuplicates and its not working. – OMO Oct 24 '18 at 15:43
The duplicates part has been covered in depth on SO, try reading this: https://stackoverflow.com/questions/31631231/remove-duplicates-from-range-of-cells-in-excel-vba – OwlsSleeping Oct 24 '18 at 15:45

Paste Range Returning Syntax Error For Variable Range

1 Answers1