How can I remove duplicates on a block of data in VBA?

Mr2017

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

I have a macro where

I paste data into 5 columns (A:E) in a tab. Then filter by the N's in column E, then remove the duplicate values in column A.

However, I've noticed the removing of duplicates isn't working, as expected.

It removes duplicates in column A, which results in several rows at the bottom of column A being blank, whilst the correspoding rows in columns B:E still have data.

All rows should still have data after the removal of duplicates.

So my question is this: how can I ensure that the removal of duplicates (based on column A) is applied to the whole block of data?

ie all columns?

What would I need to change in this line of the code, please? 'Columns("A").RemoveDuplicates Columns:=1, Header:=xlNo

Thanks in advance.

VBA Code:
With ActiveSheet
      .Range("A1:E1").AutoFilter 5, "N"
      '.AutoFilter.Range.Offset(1).EntireRow.Select
      '.AutoFilterMode = False
End With

'remove the dupes in column A

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,452
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Range("A:E").RemoveDuplicates 1, xlNo
 

Mr2017

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

Thank you very much, Fluff!

I assume the number 1 is the column that it's referencing to remove the duplicates?

I'm pretty sure it is, but just want to double-check, as I'll have to repeat the process for different tabs, but remove duplicates based on different columns.....

Thanks in advance.
 

Mr2017

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

Thank you very much for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,452
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,402
Messages
5,624,532
Members
416,032
Latest member
dunhamsasphalt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top