remove duplicate rows in array

Question

I have a question about arrays. First let me show you the code that I have got:

Dim TopStud() As Variant

TopStud = Range("A1", Range("A2").End(xlDown).End(xlToRight))
'code that removes duplicate values in array

'end code
Worksheets.Add

Range(ActiveCell, ActiveCell.Offset(UBound(TopStud, 1) - 1, UBound(TopStud, 2) - 1)).Value = TopStud

The code above shows how I get a range of values in an array. I want to remove duplicate values that is been saved in this array into a new worksheet. See above. Is there a easy way to do this?

I want the following result:

After you put the values in the cells you can use RemoveDuplicates. There are many tutorials out there and a simple search of Range.RemoveDuplicates should get them for you. — Scott Craner
– Scott Craner, Commented Sep 9, 2016 at 13:01
Record a macro that executes the remove duplicates to see how... Select data tab highlight columns A:D then select remove duplicates above Column K or there about. I use this approach often when I don't know the VBA code for something that is available though menu's. — xQbert
– xQbert, Commented Sep 9, 2016 at 13:02

score 1 · Accepted Answer · 2016-09-09 13:45:16Z

1

If you want to remove the whole record for any duplicates in the current column:

Cells.RemoveDuplicates Columns:=ActiveCell.Column,Header:=xlYes

edited Sep 9, 2016 at 13:45

answered Sep 9, 2016 at 13:27

user6432984

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Craner Over a year ago

It was not just the one column but the whole record. It would be better to use the full range and remove duplicates based on column 1. The whole record 6246,Calvin,,Bakker is the duplicate removed.

user6432984 Over a year ago

@ScottCraner Thanks, I could have sworn that I checked that.

user6432984 Over a year ago

@ScottCraner Nice article, but no it didn't change my mind. The reason being if you are searching for a number that is stored as text or a string number that is actually a number match fails. You might like EXCEL VLOOKUP VS INDEX MATCH VS SQL VS VBA

Scott Craner Over a year ago

I can see your point on the data types, for me though I would control the type on entry. then use MATCH as it is faster on single column or single row search. By dropping the WorksheetFunction part and simply using Application.Match(...) which will allow an error to pass into a variable that can be tested I have not had a problem. Really I would move it to a sql database and use sql to manage better. but that is just me.

Collectives™ on Stack Overflow

remove duplicate rows in array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related