Removing Duplicates dynamic columns VBA

[rconverse]

Hello,

I have a loop that I've set up to go through each worksheet in a workbook and perform a series of tasks. The last task is to remove duplicates. The issue is that each worksheet could have a different number of columns. Is there a way to dynamically create an array for Excel to remove duplicates?

In the first worksheet, there are 22 columns, so this works:

rng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22)

However, the next spreadsheet only has 17 columns, so that won't work. I don't want to setup a case or if statement for each worksheet because a column may get added down the road and I don't want to have to touch the code going forward. I can get the last column with data, no problem. I just need to incorporate that in the above code as opposed to hard coded numbers.

Thanks in advance for any suggestions.

Roger

 

[Waski]

I have found fully worked solution for this thread for any one who looking for clear answer.

 Dim ws As Worksheet
Dim c As Long
Dim varItems() As Variant

With ws.UsedRange
        ReDim varItems(0 To .Columns.Count - 1)
        For c = 1 To .Columns.Count
            varItems(c - 1) = c
        Next c
        .RemoveDuplicates Columns:=(varItems), Header:=xlGuess
  End With

 

[pbornemeier]

Post 11 is correct.
But why does the Columns argument have to be in parenthesis

This works:

.RemoveDuplicates Columns:=(varItems), Header:=xlGuess

This does not:

.RemoveDuplicates Columns:=varItems, Header:=xlGuess

 

[pbornemeier]

Code that uses the dynamic columns for RemoveDuplicates can be found here:
https://www.mrexcel.com/forum/excel...ions-multiple-columns-without-duplicates.html