VBA macro - posting over last cell with blank value

[jpringle1]

I have an almost perfectly functioning macro which matches rows in one table to another, and then imports any rows that aren't present in the source table onto the end of the destination table.

The ONLY problem i'm having, is that one section of the very last row that gets pasted gets correctly pasted, but then deleted again (i can see it get pasted very quickly before immediatly disappearing). There are some examples below.

[TABLE="width: 667"]
<colgroup><col span="4"><col><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 4"] Source table
[/TD]
[TD][/TD]
[TD="colspan: 3"] Destination table
[/TD]
[/TR]
[TR]
[TD]Column1[/TD]
[TD]Column2
[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD][/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD] A3
[/TD]
[TD] B3
[/TD]
[TD] -
[/TD]
[TD] C3
[/TD]
[TD][/TD]
[TD] A3
[/TD]
[TD] B3
[/TD]
[TD] C3
[/TD]
[/TR]
[TR]
[TD] A4
[/TD]
[TD] B4
[/TD]
[TD] -
[/TD]
[TD] C4
[/TD]
[TD][/TD]
[TD] A4
[/TD]
[TD] B4
[/TD]
[TD] C4
[/TD]
[/TR]
[TR]
[TD] A5
[/TD]
[TD] B5
[/TD]
[TD] -
[/TD]
[TD] C5
[/TD]
[TD][/TD]
[TD] A5
[/TD]
[TD] B5
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


As you can see, every value gets pasted correctly except for the very last one. (there is a break in the code which i made to skip column 3, as i dont want those values copied over. where the code jumps back in to start pasting values again is were the problem happens).

Sub ImportRows()
'declarations
    Dim Val As String, SourceWs As Worksheet, DestWs As Worksheet, LastRowSource As Long, LastRowDest As Long
    Set SourceWs = Sheets("Source")
    'Activate Dest workbook
    Set DestWs = Sheets("Dest")
    LastRowSource = SourceWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastRowDest = DestWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'Declaring range to analyse
    Dim i&, vSource, vDes
    '"A2:A" = starting column, resize(, 1) = amount of columns to extend the range by
    vSource = SourceWs.Range("A2:A" & LastRowSource).Resize(, 3).Value
    vDes = DestWs.Range("B2:B" & LastRowDest).Resize(, 3).Value
'Source import
With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(vDes, 1)
            Val = (vDes(i, 1)) & (vDes(i, 2)) & (vDes(i, 3))
            If Not .Exists(Val) Then
                .Add Val, Nothing
            End If
        Next i
        For i = 1 To UBound(vSource, 1)
            Val = (vSource(i, 1)) & (vSource(i, 2)) & (vSource(i, 3))
            If Not .Exists(Val) Then
                'columns to copy
                Intersect(SourceWs.Rows(i + 2), SourceWs.Range("A:A,B:B")).Copy
                'columns to start pasting in
                DestWs.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
'This next snippet likely contains the problem
                 'columns to copy
                Intersect(SourceWs.Rows(i + 2), SourceWs.Range("C:C")).Copy
                'columns to start pasting in
                DestWs.Cells(Rows.Count, "C").End(xlUp).Offset(0, 0).PasteSpecial xlPasteValues
            End If
        Next i
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Thank you forany help and sorry for the janky post

 

[Fluff]

Try changing the 1st zero (in red) to 1

DestWs.Cells(Rows.Count, "C").End(xlUp).Offset([COLOR=#ff0000]0[/COLOR], 0).PasteSpecial xlPasteValues

 

[jpringle1]

Thanks for the quick response

That ALMOST works, in fact it did technically solve the issue i had, but it has created a whole new problem. there destination data now looks like this:

[TABLE="class: cms_table, width: 667"]
<tbody>[TR]
[TD="colspan: 4"][TABLE="width: 891"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3
[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]B3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]B4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]C4[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]B5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]C5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
It's hard to tell from my representation, but basically the last piece of info no longer dissapears which is great, but every value from the C column is pasted on a seperate row below the one it should be on.

 

[Fluff]

Is the code you posted the EXACT code that you use?
The reason I ask is that you said you didn't want the values for col C copied across, but that is what you are doing.
Also, from your code you should only ever end up with 1 value in the col C of the destination.

 

[jpringle1]

Sorry, i explained that wrong.

I meant to say i dont want to copy column 3, with all the - symbols.

and also yes, this is the exact code i am using.

 

[Fluff]

Your code is copying the first 3 columns ie A:C, so you are not "skipping" the third column.
and this line of code

DestWs.Cells(Rows.Count, "C").End(xlUp).Offset(0, 0).PasteSpecial xlPasteValues

will simply overwrite the last value in col C so you will only get one value

 

[jpringle1]

Fixed it! Thank you so much, it's infuriating that such a dumb mistake has been causing me such problems.

 

[Fluff]

Glad you solved it & thanks for the feedback