0

To give context, I am a complete newb at VBA started less than 2 weeks ago to try and minimize this process. So this is daily function that gets a new list of inputs that vary from 300-5000 per day. We copy it from another software and import it into excel. First thing we do is remove the Row B as its unnecessary.

After this I remove all the duplicates in Column A. From this shorten list I copy and paste this to the last empty cell in the a new sheet,"Sheet2". From here I have to once again remove all the duplicates from the first column

I'm having some trouble with what I've written as when it gets copied over its including all the empty rows that were removed from the duplicates, so it added rows onto the table. It also is not removing the second set of duplicates from the entire Sheet2.

Here what I got:

Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A$1:$C$5000").RemoveDuplicates Columns:=1, Header:=xlNo
Selection.End(xlUp).Select

 Application.ScreenUpdating = False
 Dim copySheet As Worksheet
 Dim pasteSheet As Worksheet
 
 Set copySheet = Worksheets("Sheet1")
 Set pasteSheet = Worksheets("Sheet2")
 
 copySheet.Range("A1:B500").Copy
 pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
 
Sheets("Sheet2").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("Table2[#All]"").RemoveDuplicates Columns:=1, Header:=_
x1Yes

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
stilllearning
  • 1
  • https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 – pgSystemTester Aug 10 '21 at 14:42
  • 3
    `Header:=x1Yes` should be `Header:=xlYes` You have a "one" not an "el" – Tim Williams Aug 10 '21 at 15:09
  • 1
    Use `Option Explicit` to avoid such typos. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Aug 11 '21 at 06:59

0 Answers0