Remove duplicates from dynamic row counts

**tvanduzee** · 05-04-2022, 07:01 PM

Hello
Please let me begin by saying it has been a couple years since trying to write any vba.

I have the range (A2:Q2) that contains the first value for each column (Header is on row 1)
Each row count is different for each column.
Each column could contain duplicates (each column has to have duplicates removed individually).

Ex: Column 1
A2 = Bob
A3 = Tom
A4 = Bob
A5 = Tom
A6 = Mike

Column 2
B2 = 10
B3 = 20
B4 = 20

Column 3
C2 = Mike
C3 = Ralph
C4 = Tom
C5 = Brad

Column 1 has duplicates and need to be removed only for that column
Column 2 has duplicates and need to be removed only for that column
Column 3 doesn't contain duplicates, but it may in the future.

I have 17 columns with varying row lengths
Ive tried several ways to do this, but nothing seems to work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any Suggestions?

Thank You
Terry

**MarvinP** · 05-04-2022, 07:36 PM

Hi tvanduzee,

Try this code:

Please Login or Register  to view this content.

**MarvinP** · 05-04-2022, 07:45 PM

Hi tvanduzee,

I'm working on a better style of coding using CurrentRegion. See it below which should also work for your problem.

Please Login or Register  to view this content.

Other programmers might want to use a ScriptingDictionary to solve this problem.

**tvanduzee** · 05-04-2022, 08:01 PM

Worked Great, thank you
I'll have to study the code to understand it, but it works great.

Thank You
Terry

**mohadin** · 05-05-2022, 02:47 AM

Other programmers might want to use a ScriptingDictionary to solve this problem.

Please Login or Register  to view this content.

**MarvinP** · 05-05-2022, 11:14 AM

Hi mohadin,

I couldn't get your code to work to solve this problem. Could you supply a workbook with the code in it? I'm not good with Scripting Dictionary yet and would love to learn more.
It seemed to me that the .CurrentRegion.ClearContents would clear the entire sheet from the beginning. Also I can't get your code to compile as "k" isn't defined.

**mohadin** · 07-31-2022, 05:34 AM

Originally Posted by MarvinP

Hi mohadin,

I couldn't get your code to work to solve this problem. Could you supply a workbook with the code in it? I'm not good with Scripting Dictionary yet and would love to learn more.
It seemed to me that the .CurrentRegion.ClearContents would clear the entire sheet from the beginning. Also I can't get your code to compile as "k" isn't defined.

Hi
Terribly sorry I did not noticed till now
Accidently
k has nothing to do I just forget to delete it