Out of memory error

Hi, I got help with this code a while ago – And it works great!
However, now my data set is bigger and I’m getting an “out of memory” error.

This is what I’m doing:

- Sheet 1 and 2 have data in 20-80 columns with 100k-600k rows.
- Both sheets have an “Unique-ID” in column A – of which about 80% match.
- Purpose: New sheet 3 with the matches from sheet 1 and 2 in the same row.

Is there a way to make the code more efficient?

I'm having trouble with attaching files, does linking work?
http://www.excelforum.com/attachment...9&d=1456057518

Sub Merge_Data()
'Merges the data (rows) with identical Unique-ID in a new sheet. First X, then Y.

Application.ScreenUpdating = False

Dim LC1 As Long
Dim LCB As Long
Dim MySheetName As String
    MySheetName = "Merged Data"
    Sheets("X").Copy After:=Sheets("X")
    ActiveSheet.Name = MySheetName
        
    LR1 = Sheets("X").Cells(Rows.Count, 1).End(xlUp).Row
    LR2 = Sheets("Y").Cells(Rows.Count, 1).End(xlUp).Row
    
    LC1 = Sheets("X").Cells(1, Columns.Count).End(xlToLeft).Column
    ColLetter = GetColumnLetter6(LC1)
    LC2 = Sheets("Y").Cells(1, Columns.Count).End(xlToLeft).Column + 8
    
    LCA = LC1 + 1
    LCB = LC1 + 2
    ColLetter_2 = GetColumnLetter7(LCB)
    
    If LR1 > LR2 Then
        LRMERGE = LR1
        Else: LRMERGE = LR2
    End If
    
        Sheets("Merged Data").Range("A1:" & ColLetter & LC1).Value = Sheets("X").Range("A1:" & ColLetter & LC1).Value
        Temp = "" & ColLetter_2 & "1:" & Cells(LRMERGE, LC1 + LC2).Address
        Sheets("Merged Data").Range(Temp).FormulaR1C1 = _
                "=OFFSET(Y!R1C[" & -LCA & "],MATCH(RC1,Y!R1C1:R" & LRMERGE & "C1,0)-1,0)"

        Sheets("Merged Data").Range(Temp).Value = Sheets("Merged Data").Range(Temp).Value

        Worksheets("Merged Data").Cells(1, LCA).Value2 = "Y Matches follow:"

Application.ScreenUpdating = True
      
End Sub
Function GetColumnLetter6(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter6 = vArr(0)
End Function
Function GetColumnLetter7(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter7 = vArr(0)
End Function

Sorry about that!

http://www.excelforum.com/excel-prog...ss-memory.html

Hope that one works?