Deduplicate

[remclave]

I know this has been visited before...
Excel 2003 macro:

I routinely extract a LOT of information that requires deduplication. I get tired of walking through the same steps every time so I recorded them.


Sub DeDuplicate()
Range("A1").Select
Range("A1:C1103").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"D1"), Unique:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
End Sub

BUT! The range isn't a fixed value. So how do I convert Range("A1:C1103") for when the last cell is not c1103?

TIA
Kat

 

[VoG]

Hello and welcome to MrExcel.

Try

Sub DeDuplicate()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:C" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"D1"), Unique:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
End Sub

 

[hiker95]

remclave,

Try:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub DeDuplicate()
Dim LR As Long
LR = Cells(Rows.Count, "C").End(xlUp).Row
Range("A1:C" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True

Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
End Sub

 

[remclave]

Suhweet!!! Thank you both for your very quick responses.

I think it's time I learn about programming in basic....

Thank you both again!