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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Fixed it! Thank you so much, it's infuriating that such a dumb mistake has been causing me such problems.
 
Upvote 0
Glad you solved it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top