Removing duplicates and ignoring more than 1 row of headers

[CLoos]

Hi -

I am creating a macro to manipulate a large report. And at the end of my macro I want to remove duplicates excluding my header rows, There are four header rows. Finding possible solutions on forums, everything I am trying isn't working properly.

This is removing more than just duplicates in my data


With ActiveSheet.UsedRange
.Resize(.Rows.Count - 4).Offset(4, 0).RemoveDuplicates Columns:=8
End With

And this is removing a row out of my headers


Dim FirstCell As Range, LastCell As Range

Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
SearchDirection:=xlNext, LookIn:=xlValues).Row, _
Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, LookIn:=xlValues).Column)

Range(FirstCell, LastCell).Select

With ActiveSheet.UsedRange
.Resize(.Rows.Count - 1).Offset(4).Select
End With

Range(FirstCell, LastCell).RemoveDuplicates Columns:=8

 

[p45cal]

This is removing more than just duplicates in my data


With ActiveSheet.UsedRange
.Resize(.Rows.Count - 4).Offset(4, 0).RemoveDuplicates Columns:=8
End With

You realise that with Columns:=8 at the end of that code line, it is looking only at column 8 (column H, perhaps, if your data starts in column A) of that range to find duplicates? If you want to include more columns in that search for duplicates then you need to add them, eg.:
Columns:=Array(1, 2, 8)

 

[NARAYANK991]

Hi ,

The last line of code is the one which is removing duplicates :

Range(FirstCell, LastCell).RemoveDuplicates Columns:=8

where the highlighted range is the one which will be used to remove duplicates from.

Before this line of code , you are doing a selection of a range which excludes the header rows , in this line :

.Resize(.Rows.Count - 1).Offset(4).Select

To actually make use of this , can you not have the following as the last line :

Selection.RemoveDuplicates Columns:=8

Narayan

 

[CLoos]

You realise that with Columns:=8 at the end of that code line, it is looking only at column 8 (column H, perhaps, if your data starts in column A) of that range to find duplicates? If you want to include more columns in that search for duplicates then you need to add them, eg.:
Columns:=Array(1, 2, 8)

Thank you, I hadn't thought of this but it might work with looking at other columns.

 

[CLoos]

Hi ,

The last line of code is the one which is removing duplicates :

Range(FirstCell, LastCell).RemoveDuplicates Columns:=8

where the highlighted range is the one which will be used to remove duplicates from.

Before this line of code , you are doing a selection of a range which excludes the header rows , in this line :

.Resize(.Rows.Count - 1).Offset(4).Select

To actually make use of this , can you not have the following as the last line :

Selection.RemoveDuplicates Columns:=8

Narayan

Oh!!! I did not realize it was not "holding" the selection from my resize/offset statement. This makes sense. Thank you.