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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,502
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,502
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,608
Messages
5,548,968
Members
410,886
Latest member
sjohn627
Top