VB.net copying and pasting a column from one sheet to another sheet-CodePudding

I have been working on opening an existing workbook, while creating two new sheets inside that workbook. After that is done, I am trying to copy an entire column from an existing sheet in the workbook to one of the new sheets I have created then removing duplicates in that column along with empty rows. I have accomplished creating the two new sheets, but my copy doesn't seem to work, the column is empty. I also have errors on removing the empty rows. All insight would be helpful!

'Open up the workbook and create two need worksheets (Unique Numbers & Attributes).
        If IO.File.Exists("C:\Users\file.xlsm") Then xlWorkBook = xlApp.Workbooks.Open("C:\Users\file.xlsm")
        Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets
        Dim xlNewSheet = DirectCast(worksheets.Add(worksheets(1), Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet)
        xlNewSheet.Name = "Unique Numbers"
        Dim xlNewSheet2 = DirectCast(worksheets.Add(worksheets(1), Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet)
        xlNewSheet2.Name = "Attributes"

        'Populate the Unique Numbers worksheet.
        Dim trace As Object
        Dim unique_numbers As Object
        trace = xlWorkBook.Sheets("2020-12-16_12-07-12_781") 'The existing worksheet in the workbook
        unique_numbers = xlWorkBook.Sheets("Unique Numbers")

        'Extract the values from trace and copy them to unique numbers, then remove duplicates and empty rows
        trace.Range("E:E").Copy 'I can confirm this row has data
        unique_numbers.Range("A:A").PasteSpecial(Excel.XlPasteType.xlPasteFormats)
        unique_pgns.Range("A:A").RemoveDuplicates()
        'Loop through range ("A")
         Dim rowCount As Integer = unique_numbers.Rows.Count()
         Dim r As Integer
         For r = rowCount - 1 To 0 Step -1
             If unique_numbers.Rows(r) Is "" Then
                 unique_numbers.DeleteRow(r   1)
            End If
        Next

CodePudding user response：

Finally figured it out after multiple, multiple tries.

trace.Range("E:E").Copy()
unique_numbers.Range("A:A").PasteSpecial(Excel.XlPasteType.xlPasteAll)
unique_numbers.Range("A:A").RemoveDuplicates(Columns:=1,Header:=Excel.XlYesNoGuess.xlYes)