Macro to delete Duplicate Collumns

[howard]

I have headings in row1 in sheet1


I would lie a macro to delete duplicate headings i.e only retain one unique header


For eg if "branch" appears in A1 and D1 , then only delete the second duplicate i.e Col D, eg Surname is in B1 and E1, the only delete col E etc


your assistance is most appreciated

 

[Fluff]

How about

Sub Howard1()
   Dim Rng As Range
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
         If Not .Exists(Cells(1, i).Value) Then
            .Add Cells(1, i).Value, Nothing
         Else
            If Rng Is Nothing Then Set Rng = Columns(i) Else Set Rng = Union(Rng, Columns(i))
         End If
      Next i
   End With
   If Not Rng Is Nothing Then Rng.Delete
End Sub

 

[howard]

Thanks for the help, Code works perfectly


When is With CreateObject("scripting.dictionary") used in code and what is its function ?

 

[Fluff]

You're welcome & thanks for the feedback.
For more info on dictionaries have a look at https://excelmacromastery.com/vba-dictionary/

 

[howard]

Thanks for the link

 

[Fluff]

You're welcome