TRIM & Remove Duplicate and TEXTJOIN a variable range using VBA

I would like a range most varying say A1:A20 pulled from an application with space in beginning or in end so it need to be TRIM and then Remove Duplicates, remove blank cells and they need to be TEXTJOIN(",",TRUE,A1:A20). Range is variable. So that I can copy paste this to QMF for running query.

Appreciate your help in creating this VBA so that I can create a button in the ribbon and can use in any excel sheet.

1 answer

  • answered 2019-04-21 10:14 David Jones

     Sub removeDuplicate()
     'removeDuplicate Macro
    
     Columns("A:A").Select
     ActiveSheet.Range("$A$1:$A$20").RemoveDuplicates Columns:=Array(1), _ 
     Header:=xlNo 
     Range("A1").Select
    
     End Sub