merging csv data with spreadsheet using vba

[blueflash]

I have a spreadsheet with the following codes in column A:
AA
BC
DE
DJ
GG
HH

I want to merge data from a csv file with the following codes at the start of each line:
BB
BC
DF
EA
FA
GG
HH
LL
These codes will always be in alphabetical order in both files.
When merging, if the code in the spreadsheet does not match a code in the csv file, that row should be deleted from the spreadsheet.
If the code in the csv file does not match a code in the spreadsheet, that line from the csv file should be inserted into the spreadsheet in the correct position.
In this example, the spreadsheet after merging should contain rows:
AA
BB
BC
DF
EA
FA
GG
HH
LL

Can anyone help with some slick code to do this please?
Thanks

 

[Submeg]

Try:

EDIT: Sorry, I missed these two lines, but it will give you a sorted list.

When merging, if the code in the spreadsheet does not match a code in the csv file, that row should be deleted from the spreadsheet.
If the code in the csv file does not match a code in the spreadsheet, that line from the csv file should be inserted into the spreadsheet in the correct position.

Sub Macro1()

Dim FD As Office.FileDialog

Dim WB_Destination As Workbook
Dim WB_CSV As Workbook

Dim LastRow_CSV As Long
Dim LastRow_Destination As Long

LastRow_CSV = 0
LastRow_Destination = 0

Set WB_Destination = ActiveWorkbook

'Find the range.
Range("A1048576").Select
Selection.End(xlUp).Select
LastRow_Destination = ActiveCell.Row

Range("A1").Select

'Select and open CSV.
Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Show
End With

Workbooks.Open (FD.SelectedItems(1))
Set WB_CSV = ActiveWorkbook

'Find the range.
Range("A1048576").Select
Selection.End(xlUp).Select
LastRow_CSV = ActiveCell.Row

'Copy the range
Range("A1:A" & LastRow_CSV).Select
Selection.Copy

'Paste the range.
Workbooks(WB_Destination.Name).Activate
Range("A" & LastRow_Destination + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Workbooks(WB_CSV.Name).Close

'Find the updated range.
Range("A1048576").Select
Selection.End(xlUp).Select
LastRow_Destination = ActiveCell.Row

'Sort the range alphabetically
Range("A2:A" & LastRow_Destination).Select

On Error Resume Next

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

On Error GoTo 0

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A1:A" & LastRow_Destination), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:A" & LastRow_Destination)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Save
  
End Sub

 

[Fuji]

When merging, if the code in the spreadsheet does not match a code in the csv file, that row should be deleted from the spreadsheet.
If the code in the csv file does not match a code in the spreadsheet, that line from the csv file should be inserted into the spreadsheet in the correct position.
In this example, the spreadsheet after merging should contain rows:
AA
BB
BC
DF
EA
FA
GG
HH
LL

I don't understand why AA should remain...
Do you want to replace the current data with the csv data? or am I missing something?

 

[blueflash]

I don't understand why AA should remain...
Do you want to replace the current data with the csv data? or am I missing something?

Sorry Fuji it was an omission in the csv file - the first line ofthe csv file should be AA

 

[Fuji]

OK,

If so, like I mentioned, is it to replace current data with the csv data?

 

[blueflash]

Try:

EDIT: Sorry, I missed these two lines, but it will give you a sorted list.

Sub Macro1()

Dim FD As Office.FileDialog

Dim WB_Destination As Workbook
Dim WB_CSV As Workbook

Dim LastRow_CSV As Long
Dim LastRow_Destination As Long

LastRow_CSV = 0
LastRow_Destination = 0

Set WB_Destination = ActiveWorkbook

'Find the range.
Range("A1048576").Select
Selection.End(xlUp).Select
LastRow_Destination = ActiveCell.Row

Range("A1").Select

'Select and open CSV.
Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Show
End With

Workbooks.Open (FD.SelectedItems(1))
Set WB_CSV = ActiveWorkbook

'Find the range.
Range("A1048576").Select
Selection.End(xlUp).Select
LastRow_CSV = ActiveCell.Row

'Copy the range
Range("A1:A" & LastRow_CSV).Select
Selection.Copy

'Paste the range.
Workbooks(WB_Destination.Name).Activate
Range("A" & LastRow_Destination + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Workbooks(WB_CSV.Name).Close

'Find the updated range.
Range("A1048576").Select
Selection.End(xlUp).Select
LastRow_Destination = ActiveCell.Row

'Sort the range alphabetically
Range("A2:A" & LastRow_Destination).Select

On Error Resume Next

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

On Error GoTo 0

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A1:A" & LastRow_Destination), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:A" & LastRow_Destination)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Save
 
End Sub

Thanks submeg, I will give it a go

 

[blueflash]

OK,

If so, like I mentioned, is it to replace current data with the csv data?

Current data should be replaced with csv data

 

[Fuji]

Sub test()
    Dim fn$, s$
    fn = Application.GetOpenFilename("CSVFile,*.csv")
    If fn = "False" Then Exit Sub
    x = Split(CreateObject("Scripting.FileSystemObject").OpentextFile(fn).ReadAll, vbNewLine)
    With Sheets(1).[a1]
        .EntireColumn.ClearContents
        .Resize(UBound(x) + 1) = Application.Transpose(x)
    End With
End Sub