I wanted to cover 2 questions

1. I would like to create a macro in personal.xlsb which can remove duplicate of the selected range and put in Clipboard without removing the duplicate from the source range selected. Only the clipboard values should have the duplicates removed. I created a transpose of the selected range with commas for each of them but when I execute I see that the source I selected is removed the duplicate and it affects my source. But I only want the results in the clipboard to have the duplicates removed. Below is the code

   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   <code>Sub cpbd()

   With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

   Selection.Rows.RemoveDuplicates Columns:=1, Header:=xlNo

   ''Range(Selection.Rows(1), Selection.End(xlDown)).Select

   .SetText Join(Evaluate("transpose(" & Selection.Address & ")"), ",")

   .PutInClipboard

   End With

   End Sub

   </code>

   <code>Sub cpbd() With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") Selection.Rows.RemoveDuplicates Columns:=1, Header:=xlNo ''Range(Selection.Rows(1), Selection.End(xlDown)).Select .SetText Join(Evaluate("transpose(" & Selection.Address & ")"), ",") .PutInClipboard End With End Sub </code>

   Sub cpbd()
   
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
         Selection.Rows.RemoveDuplicates Columns:=1, Header:=xlNo
         ''Range(Selection.Rows(1), Selection.End(xlDown)).Select
        .SetText Join(Evaluate("transpose(" & Selection.Address & ")"), ",")
        .PutInClipboard
       End With
   End Sub
   

   Also I find that this vba is not always working for some reason and I dont know if its the problem with the code GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

2. I would like a shortcut Ctrl + E to use for this in Excel but I see that Ctrl + E is already used as a shortcut for flash fill. I do not use flash fill shortcut. I would like to assign Ctrl + E for the above script.

If there is a better and robust VB script which you can provide I really appreciate it.