VBA code to dynamically remove duplicates?

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I recorded a macro to remove duplicates from data in column A.

The data set was very simple - I had the number 1 in cells A1, and A2, respectively.

And the number 2 in cells A3, and A4, respectively.

However, this code only works on a fixed range. Does anyone know how to make it dynamic, please?

VBA Code:
Sub remove_duplicates()

    Columns("A:A").Select
    ActiveSheet.Range("$A$1:$A$4").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
Just use the entire column
VBA Code:
    Columns("A").RemoveDuplicates Columns:=1, Header:=xlNo
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
You;re welcome.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Fluff

Do you know how I can amend this if I have data in columns A-G with duplicates, but I want to delete duplicates from the rows based on data in column G, please?

VBA Code:
Columns("A").RemoveDuplicates Columns:=1, Header:=xlNo
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,678
I have data in columns A-G with duplicates, but I want to delete duplicates from the rows based on data in column G

Try this:

VBA Code:
Option Explicit
Sub Macro1()

    Columns("A:G").RemoveDuplicates Columns:=7, Header:=xlNo

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,170
Members
410,775
Latest member
alal1030
Top