Dynamic "Columns:=Array(1,2,3)" in excel VBA

[mhrsolanki2020]

Hello,
I am working on a project in which I need to keep a list of unique patient. As and when new entries are added to the main database sheet, i will add the name to the patientMaster list and then use remove duplicate method of Range function. The code being

patientMaster.Range(patientMaster).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

The problem is, currently I have 3 Columns viz, Name, Date Of Birth and Family Details. However, down the line, new columns might be added. Say i add 2 more columns, then i would have to re-edit the code to

patientMaster.Range(patientMaster).RemoveDuplicates Columns:=Array(1, 2, 3,[U][B]4,5[/B][/U]), Header:=xlYes

My question is: " Is there any way that I can do this dynamically?"

I do have a solution using IIf statements which counts the number of columns and then sets the Array

'numOfCol has the number of columns
patientMaster.Range(patientMaster).RemoveDuplicates Columns:=IIf(numOfCol = 7, (Array(1, 2, 3,4,5,6,7),IIf(numOfCol = 6,Array(1,2,3,4,5,6),IIf(numOfCol = 5,Array(1,2,3,4,5),IIf(numOfCol=4,Array(1,2,3,4),Array(1,2,3)))))  , Header:=xlYes

But this is very inefficient. If you can help me with any other efficient solution?

Mihir Solanki

 

[AlphaFrog]

    'numOfCol has the number of columns
   
    Dim MyArray As Variant
    MyArray = Evaluate("Row(1:" & numOfCol & ")") 'Formula that returnas an array seq.
    patientMaster.Range(patientMaster).RemoveDuplicates Columns:=MyArray, Header:=xlYes

 

[mhrsolanki2020]

    [COLOR=green]'numOfCol has the number of columns[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] MyArray [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    MyArray = Evaluate("Row(1:" & numOfCol & ")") [COLOR=green]'Formula that returnas an array seq.[/COLOR]
    patientMaster.Range(patientMaster).RemoveDuplicates Columns:=MyArray, Header:=xlYes

Thanks for you reply. Can you explain what is happening in here assuming there are 6 columns

 Evaluate("Row(1:6)")

is it that it will return (1,2,3,4,5,6) ?? how?
It is not apparent from the documentation I read here https://msdn.microsoft.com/en-us/library/office/ff193019.aspx

 

[AlphaFrog]

- The Evaluate command can evaluate a worksheet formula and return the result.
- The worksheet formula, in this case, uses the ROW function.
- ROW(1:6) evaluates to an array of sequential numbers (1,2,3,4,5,6).

 

[mhrsolanki2020]

- The Evaluate command can evaluate a worksheet formula and return the result.
- The worksheet formula, in this case, uses the ROW function.
- ROW(1:6) evaluates to an array of sequential numbers (1,2,3,4,5,6).

Got it .. Thank you for your help

 

[rahulpriyadarshi3785]

x=Evaluate ("Row(1:" & lastColumn & ")")
.Range(Cells(1,1), Cells (lastRow,lastColumn)). RemoveDuplicates(Columns:=x, Headers:=xlNo)

 

[rahulpriyadarshi3785]

x=Evaluate ("Row(1:" & lastColumn & ")")
.Range(Cells(1,1), Cells (lastRow,lastColumn)). RemoveDuplicates(Columns:=x, Headers:=xlNo)

It's not working for me. I have been following the post and wanted to try the same.

 

[AlphaFrog]

.Range(.Cells(1,1), .Cells(lastRow,lastColumn)).RemoveDuplicates Columns:=x, Header:=xlNo

 

[rahulpriyadarshi3785]

.Range(.Cells(1,1), .Cells(lastRow,lastColumn)).RemoveDuplicates Columns:=x, Header:=xlNo

It's showing same error. I can share you whole post.

Dynamic "Columns:=Array(1,2,3)" in RemoveDuplicates excel VBA

I am new to VBA. Currently I am using this, lastColumn is last column of worksheet. lastRow is last row of worksheet. It is working fine for: ActiveSheet.Range(Cells(1,1), Cells (lastRow,lastColumn)). RemoveDuplicates(Columns:=Array(1,2,3), Headers:=xlNo) - But it will work only for 3 columns...

www.mrexcel.com

I started a new post there.

Also,
Dim x As Variant