CODEWITHSUNDEEP
excelvba
G.M
G.M

Reputation: 365

Remove duplicates and sort rows

I have two separate problems but they may have the same cause.

I first need to delete all duplicated rows of a Sheet. Column A contains an index value, which is imported as a text value so "1", "2"... etc.

I searched and got this line of code:

VarAnzahlZeilen = 50 '(actually calculated values but they are correct (F8))
VarAnzahlSpalten =50 
     Worksheets("Filter").Range(Cells(2, 1), Cells(VarAnzahlZeilen, VarAnzahlSpalten)).RemoveDuplicates Columns:=Array(1), Header _
        :=xlNo

Nothing happens, all duplicated rows are still in the sheet.

Same with the next line of code which should sort the remaining rows:

Worksheets("Filter").Range(Cells(2, 1), Cells(2, VarAnzahlSpalten)).Sort Key1:=Range("A2"), Header:=xlNo

Nothing happens.

A test msgbox right there, is shown and the worksheet/Range combo is also correct (I can change values).

The whole row would be a duplicate since the import works that way but since the index is in column A, I choose this as the indicator for a duplicate.

This is my current version:

With Worksheets("Filter")
    .Range(.Cells(2, 1), .Cells(VarAnzahlZeilen, VarAnzahlSpalten)).RemoveDuplicates Columns:=Array(1), Header:=xlNo
    .Range(.Cells(2, 1), .Cells(VarAnzahlZeilen, VarAnzahlSpalten)).Sort Key1:=Range("A2"), Header:=xlNo
End With

Upvotes: 0

Views: 131

Answers (1)

DisplayName
DisplayName

Reputation: 13386

Always qualifiy your range references up to sheet object, otherwise ActiveSheet reference would be implicitly assumed:

With Worksheets("Filter")
    .Range(.Cells(2, 1), .Cells(VarAnzahlZeilen, VarAnzahlSpalten)).RemoveDuplicates Columns:=Array(1), Header:=xlNo
End With

The dot in “.Cells()” is referencing the object referenced in “With” statement(i.e. “Worksheets("Filter")“)

Upvotes: 1

Related Questions