[SOLVED] VBA combine data and remove duplicates

Hello, I need help!
I work in Excel all the time but am not a specialist in VBA.

What is the goal - to have a macro that would combine data from two or more separate Excel files into a new file, pasting data one set after another. And then remove duplicates from the combined spreadsheet. All datasets have an equal number of columns but a different number of rows. This macro I want to save into my colleagues Excel personal.xlsb files so that they could perform these operations routinely. Most of them are not really good with Excel so the macro should ease the process for them.

So I found the code below for combining data. It works fine if I save this macro within a workbook where the datasets will be combined. However, if I save this macro to the personal.xlsb it combines data in that personal.xlsb. Unfortunately, I am rather ignorant in VBA and don't know how to fix it. The idea is that a person opens a new workbook, clicks the macro shortcut, the is oofered to choose files to combine data and data is combined on a spreadsheet in that new workbook. Please help and correct the coding.

Then I myself recorded a macro to remove duplicate rows. But then when I apply it to a new data set which can be larger, it doesn't work. Would you help correcting it so it can work with any number of rows (and maybe any number of columns), please?

At the end I plan to combine the macros and run it as one.

First code (CombineData)

Sub CombineData()
'Declare variables
Dim wb As Workbook
Dim ws As Worksheet
Dim fd As FileDialog
Dim file As Variant
Dim rng As Range
Dim LastRow As Long
Dim LastCol As Long

'Create a new sheet for the combined data
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Combined Data"

'Create a file dialog to select the files to combine
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = True
fd.Title = "Select the files to combine"

'Show the file dialog and get the selected files
If fd.Show = -1 Then
For Each file In fd.SelectedItems
'Open the selected file as read-only
Set wb = Workbooks.Open(file, ReadOnly:=True)
'Get the used range of the first sheet
Set rng = wb.Sheets(1).UsedRange
'Get the last row and column of the combined data sheet
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

'If the combined data sheet is empty, copy the headers and data from the first file
If LastRow = 1 And LastCol = 1 And ws.Cells(1, 1) = "" Then
rng.Copy ws.Cells(1, 1)
Else
'Otherwise, copy only the data (without headers) from the other files and append it to the combined data sheet
rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count).Copy ws.Cells(LastRow + 1, 1)
End If

'Close the selected file without saving changes
wb.Close SaveChanges:=False
Next file

'Inform the user that the data has been combined successfully
MsgBox "The data has been combined successfully in the sheet ""Combined Data"".", vbInformation, "Done"

Else
'If no files are selected, inform the user and exit the sub
MsgBox "No files were selected. Please try again.", vbExclamation, "Canceled"
Exit Sub
End If

End Sub



Second code (RemoveDuplicates)

Sub RemoveDuplicates()
Cells.Select
ActiveSheet.Range("$A$1:$V$5907").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _
, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22), Header:=xlYes
End Sub


Thank you!!!

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thank you, Bernie!!!

Could you please help also with the second code - to remove duplicate rows? How to make it work with any number of rows (and maybe any number of columns)?

This is the code I recorded but it does not work with datasets which have larger number of rows

Sub RemoveDuplicates()
Cells.Select
ActiveSheet.Range("$A$1:$V$5907").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _
, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22), Header:=xlYes
End Sub

Put this at the bottom of your first macro:

Please Login or Register  to view this content.

Thank you, Bernie!

It leads to an error:
Run-time error 5:
Invalid procedure call or argument

The following line is highlighted
ws.UsedRange.RemoveDuplicates Columns:=(varArray), Header:=xlYes

Hello. Try.

Please Login or Register  to view this content.

And also, return to your post #1 and format your macro code with the appropriate tag, see the screenshot below.

Here is a working macro, all combined:

Please Login or Register  to view this content.