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
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)