VBA macro - posting over last cell with blank value

jpringle1

New Member
Joined
Jan 4, 2019
Messages
7
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.

Source table
Destination table
Column1Column2
Column3Column4Column1Column2Column3
A3
B3
-
C3
A3
B3
C3
A4
B4
-
C4
A4
B4
C4
A5
B5
-
C5
A5
B5

<colgroup><col span="4"><col><col span="3"></colgroup><tbody>
</tbody>


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).

HTML:
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,108
Office Version
  1. 365
Platform
  1. Windows
Try changing the 1st zero (in red) to 1
Code:
DestWs.Cells(Rows.Count, "C").End(xlUp).Offset([COLOR=#ff0000]0[/COLOR], 0).PasteSpecial xlPasteValues
 

jpringle1

New Member
Joined
Jan 4, 2019
Messages
7
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:

Column1Column2Column3
A3B3
C3
A4B4
C4
A5B5
C5

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,108
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

jpringle1

New Member
Joined
Jan 4, 2019
Messages
7

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,108
Office Version
  1. 365
Platform
  1. Windows
Your code is copying the first 3 columns ie A:C, so you are not "skipping" the third column.
and this line of code
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

New Member
Joined
Jan 4, 2019
Messages
7
Fixed it! Thank you so much, it's infuriating that such a dumb mistake has been causing me such problems.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,108
Office Version
  1. 365
Platform
  1. Windows
Glad you solved it & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,677
Messages
5,524,225
Members
409,566
Latest member
santoshsj

This Week's Hot Topics

Top