Macro - Deleting Duplicate Cell Content

[Gessen]

Hi Guys,

Conceptually this doesn't sound too crazy to me, I just don't know VBA. So I could really use your help! I need a Macro to delete duplicate cell content (String) within a column section, until it hits an empty ("") cell, with a starting cell based off of a specified column and row number. Can anyone help me put something together? I've got a sheet that pulls a lot of data from other sheets and creates some duplicate content. I just want to be able to clean them up. The duplicate content might not be one after another. The duplicate may be at the opposite end of the list. Thanks for your help!

-Gessen

 

[Gessen]

Sub Macro2()
'
' Macro2 Macro
'
'
ActiveSheet.Range("$BA$1016:$BA$1041").RemoveDuplicates Columns:=1, Header _
:=xlNo
End Sub


I got it to work by highlighting specific sections of the column versus the entire column. Here is the macro I get. I is there a way to have it end on an empty cell after defining the start cell?

 

[Gessen]

Maybe an IF statement that evaluates the current cell and jumps out of the macro if it is empty?

 

[Smitty]

You can use a LastRow assignment:

ActiveSheet.Range("$BA$1016:$BA$" & Range("BA1016").End(xlDown).Row).RemoveDuplicates Columns:=1, Header:=xlNo

 

[Gessen]

Sorry, another issue. This method seems to erase any conditional formatting I have in those cells. So when they get populated with valid data, they aren't formatting correctly. Sorry and thanks for your help.

 

[Smitty]

You'll probably need to add that back in programatically as well.

 

[Gessen]

You'll probably need to add that back in programatically as well.

Ughh and it gets more complicated. Haha, I'll play around with it. It probably isn't worth me worrying about to much. I'm just trying to automate some of my coworkers workload in managing our tracking sheet. Thank you for your help Smitty!

 

[Smitty]

I think it would be more complicated to try to loop through your data and identify and delete duplicates, because you can't just delete rows since you have multiple columns with potential duplicate data. So even if you have to add back in the CF, it's probably easier to use the Duplicate Wizard, since it will do all of that for you.

 

[Gessen]

My problem is that I don't know how to do conditional formating in VBA. I'm a VB/Excel scrub who got handed creation/ownership of this tracking sheet because I know other languages. My day-to-day responsibilities have nothing to do with this haha.

Can I use the Macro recorder to get some conditional formatting templates?

 

[Smitty]

Can I use the Macro recorder to get some conditional formatting templates?

Yup

 

[Gessen]

Cool, I'll see if I can work that out. Thanks man.