VBA - Deduplicate a column without exact references

[rachel06]

Hi!

This is probably another easy one, but again, nothing I'm searching for is helping.

I want to remove duplicates from a column but I don't want it to have exact cell references. It's going to be a different number of rows for each dataset each time.

Everything I've tried either slows the macro way down, completely crashes excel, or crashes the macro.

This little bit is part of a larger code. My data starts in cell E1:

Columns("E:E").Select
    ActiveSheet.Range("$E$2:$E$28605").RemoveDuplicates Columns:=1, Header:=xlNo

I know this is super simple and super basic and I feel so silly that I can't figure it out. Hoping there's an easy fix to update!

 

[dreid1011]

Is there a reason not to use the Remove Duplicates tool on the Data tab?

You can find the last row of data like this:

Dim lRow As Long
lRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("E2:E" & lRow).RemoveDuplicates Columns:=1, Header:=xlNo

 

[Joe4]

No need to select the column first.
Try this method that will dynamically find the last row in column E with data, and only apply it on that:

'   Find last row in column E with data
    Dim lr As Long
    lr = Cells(Rows.Count, "E").End(xlUp).Row
   
'   Remove duplicates from column E
    Range("E2:E" & lr).RemoveDuplicates Columns:=1, Header:=xlNo

 

[Joe4]

dreid1011,

Have a closer look at this line - you have some typos in it:

lRow = ActiveSheet.Range("E2" & Rows.Count).End(xlUp).Row

 

[rachel06]

Is there a reason not to use the Remove Duplicates tool on the Data tab?
View attachment 112792

You can find the last row of data like this:

Dim lRow As Long
lRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("E2:E" & lRow).RemoveDuplicates Columns:=1, Header:=xlNo

Yeah, it's part of a larger code. This step is right in the middle of it so I have things it needs to do after

 

[rachel06]

No need to select the column first.
Try this method that will dynamically find the last row in column E with data, and only apply it on that:

'   Find last row in column E with data
    Dim lr As Long
    lr = Cells(Rows.Count, "E").End(xlUp).Row
  
'   Remove duplicates from column E
    Range("E2:E" & lr).RemoveDuplicates Columns:=1, Header:=xlNo

This seems to work perfectly. Thanks so much!!!

 

[dreid1011]

dreid1011,

Have a closer look at this line - you have some typos in it:

lRow = ActiveSheet.Range("E2" & Rows.Count).End(xlUp).Row

Fixed it already, thanks.

 

[Joe4]

Fixed it already, thanks.

Not quite. Try it for yourself, you will see that you get an error (always a good idea to test your code first before posting it!).
You are setting it to be an invalid row number.

Range("E2" & Rows.Count)

should be:

Range("E" & Rows.Count)

Otherwise, you are starting with row number 21048576, which is not a valid row number on an Excel sheet!

 

[Joe4]

This seems to work perfectly. Thanks so much!!!

You are welcome.
Glad we were able to help!

 

[dreid1011]

Not quite. Try it for yourself, you will see that you get an error (always a good idea to test your code first before posting it!).
You are setting it to be an invalid row number.

Range("E2" & Rows.Count)

should be:

Range("E" & Rows.Count)

Otherwise, you are starting with row number 21048576, which is not a valid row number on an Excel sheet!

I made the edit right after you copied me to point it out.