VBA Preventing duplicates

Hknur

New Member
Joined
May 15, 2018
Messages
1
I have a monthly data of 178 rows and 7 columns. I am trying to add monthly data to big database by macro. I need to prevent duplicates. My macro is like following until now. I could remove duplicates but I cannot figure out how to prevent. I need to prevent exactly matching rows. Do you have any idea?

Thanks

Sub Import_Data()
'
' Import_Data Macro
'


'
ChDir "C:\Users\HKNur\Documents\VBA AHG\VBA"

fox = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Workbooks.OpenText Filename:= _
fox, Origin:=437, _
StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11 _
, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:= _
True
ActiveSheet.Move Before:=Workbooks("Monthly Update.xlsm").Sheets(1)
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub
Sub fill_blanks()
'
' fill_blanks Macro
'


'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select

End Sub
Sub Add_Date()
'
' Add_Date Macro
'


'
donkey = Left(ActiveSheet.Name, 7)
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A2").Select
ActiveCell.FormulaR1C1 = donkey
Range("A3").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

End Sub
Sub Append_Data()
'
' Append_Data Macro
'


'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Users\HKNur\Documents\VBA AHG\VBA\Orders.xlsx"
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Selection.CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
7), Header:=xlNo

ActiveWindow.Close savechanges:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub
 
Last edited by a moderator:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the board

Is your data in columns A to G?
Does row 1 contain column headings?
How is the data input?
What should happen if current row is identical to another row? Delete active row? Delete contents in specific columns in active row?
 
Upvote 0
It appears that this seems to be a school/homework assignment that a lot of people are asking about.
There are 4 of you who all joined yesterday, all located in the same city, all asking similar questions:
https://www.mrexcel.com/forum/excel-questions/1055743-how-prevent-duplicate-vba-excel.html
https://www.mrexcel.com/forum/excel-questions/1055758-removing-duplicates.html
https://www.mrexcel.com/forum/excel-questions/1055746-prevent-duplicate-data-using-vba.html

Note that while people may be willing to give you "hints" or point you in the right direction, please do not expect people to complete your homework assignments for you.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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