-2

There are 2 set of codes below, the upper one (as comments) doesn't work, while the one below works. My goal is to remove duplicates from a range that I do not know its number of columns beforehand. The one above should also create an array similar to Array(1,2,3,4,5). Any idea why the one at the bottom works while the one on top doesn't? Thanks in advance! (Pasted the wrong code block earlier.)

The error I got is Run Time error 5: Invalid procedure call or argument.

Sub RemoveDup(datarange As Range)

    Dim ColArray() As Variant
    Dim i As Integer

'    ReDim ColArray(1 To datarange.Columns.Count())
'
'    For i = 1 To datarange.Columns.Count()
'        ColArray(i) = i
'    Next i

    ColArray = Array(1, 2, 3, 4, 5)

    datarange.RemoveDuplicates Columns:=(ColArray), _
        Header:=xlNo

End Sub
Nick123
  • 197
  • 1
  • 10
  • 1
    Use (ColArray) i.e. put () round it. – QHarr Sep 29 '18 at 09:27
  • I think the question is about how to remove duplicates from a range taking all columns into account. The code above is not working as explained in the link to the other question but there is no code how to remove duplicates for all columns. – Storax Sep 29 '18 at 09:35
  • @Storax I could be wrong, and would VTR, but if OP says bottom version works and by wrapping the top version inside the () you get the same outcome, does that not mean that the answer is to wrap in () as per the proposed duplicate? – QHarr Sep 29 '18 at 09:57
  • @QHarr: Right, code works for the case OP has but OP would like to use the code for a range where the number of colums is not known beforehand. And on SO I did not find a solution for this although it is quite straight how to do it. But maybe I did not search thoroughly enough. – Storax Sep 29 '18 at 10:03
  • I'm torn.. I see what you mean. The question stated is: Any idea why the one at the bottom doesn't work while the one on top does? That is the basis I used for marking as duplicate. But the actual objective will be to generate the array on the fly. You would still need to wrap in () if using an variant array variable in the removeDuplicates part though, no? – QHarr Sep 29 '18 at 10:06
  • Yes, that's right, you would create an array with the columns of the range and put this array into parentheses when using it in RemoveDuplicates. So you are porbably right, the OP should ask a new question and be more specific. – Storax Sep 29 '18 at 10:08
  • I think if Nick123 has a further question later about how to identify the columns or some such, that's a different question. QHarr's dupetarget and comment address the question above: Why does the first one above fail but the second succeed. – T.J. Crowder Sep 29 '18 at 10:27
  • I have updated my code. Hope it's not really a duplicate! – Nick123 Sep 29 '18 at 11:04
  • 1
    I have lifted the close vote in light of the edit as linked answer is no longer viable. Please can you edit to make it clearer the problem though. Essentially you are now saying that if you generate the array with the loop over datarange.Columns.Count() then try datarange.RemoveDuplicates Columns:=(ColArray) you get runtime error 5 invalid procedure call. You do not get this array when passing the array using ColArray = Array(1, 2, 3, 4, 5) and then atarange.RemoveDuplicates Columns:= ColArray. Can you edit please to show both top and bottom in full with error message? – QHarr Sep 29 '18 at 11:24
  • At least that is my understanding of the actual problem. – QHarr Sep 29 '18 at 11:24
  • What about including `Evaluate` in the statement `datarange.RemoveDuplicates Columns:=Evaluate(ColArray), Header:=xlNo` ? – T.M. Sep 29 '18 at 11:39
  • 1
    Tried adding in evaluate, it ran smoothly, but it deleted the rows that aren't really duplicates. – Nick123 Sep 29 '18 at 11:47
  • 1
    Please tidy up the question (as already advised) and add some sample data with expected outcome . It will make answering this accurately easier. – QHarr Sep 29 '18 at 12:00

1 Answers1

1

You need to start building the array from 0... as follows:

Dim datarange As Range
Dim ColArray() As Variant
Dim i As Integer

Set datarange = Range("A1:E6")
ReDim ColArray(0 To datarange.Columns.Count() - 1)

For i = 0 To datarange.Columns.Count() - 1
    ColArray(i) = i + 1
Next i

datarange.RemoveDuplicates Columns:=(ColArray), Header:=xlNo

Note: I've just input the range as Range("A1:A6") but you can specify whatever you want.

Peter
  • 26
  • 6
  • This worked! Any idea why using 1 as a starting point fails to work? Thanks! The error I got is Run Time error 5: Invalid procedure call or argument. – Nick123 Sep 29 '18 at 13:24
  • Hi @Nick123, I'm glad to hear if worked for you. From what I understand, if you don't declare the dimension of the array - e.g. Dim ColArray(1 to 5) - in the initial declaration statements, its lower bound shall always be 0 regardless of any subsequent redim statements. This I surmise from experience, there may well be more to it than that however. – Peter Sep 29 '18 at 13:42