VBA for deleting duplicate rows

[Guest]

Hi
Amateur needs some help please: Sheet40, col B contains item id:s - I need
to keep lowest numbers and delete all duplicate rows, which can be one or
more. Should be controlled by code - not a manually started macro. Understand
I should start with sorting - but then?
/ulf

 

[Bernie Deitrick]

Keep the lowest number, based on the value in which column?

Should be controlled by code - not a manually started macro.

What do you mean by that?

Bernie
MS Excel MVP

 

[meh2030]

Hi
Amateur needs some help please: Sheet40, col B contains item id:s - I need
to keep lowest numbers and delete all duplicate rows, which can be one or
more. Should be controlled by code - not a manually started macro. Understand
I should start with sorting - but then?
/ulf

This assumes that you are deleting duplicates in Col B on Sheet40.

After you sort the data by Col B you can do something like the
following (This is not tested):

Sub delDuplicates()

Dim counter As Integer
Dim a As Integer
Dim currCell As Variant
Dim nextCell As Variant
Dim delRng As Range

counter = Range("b1").CurrentRegion.Rows.Count

For a = counter To 2 Step -1
currCell = Range("b" & a).Value
nextCell = Range("b" & a).Offset(-1, 0).Value

Set delRng = Range("b" & a).Offset(-1, 0)

If currCell = nextCell Then
'Range("b" & a).Offset(-1, 0).EntireRow.Delete
delRng.EntireRow.Delete
End If
Next

End Sub

 

[Guest]

Sorry - let me try to explain more carefully_

- I need to keep rows with lowest row number after sorting on B and M
(sometimes another cols) and delete possible other rows with same id. Deleted
rows should not leave empty rows.

- processing is automated - sub should be called from other sub with no user
involvment

thank you!
/ulf

 

[Bernie Deitrick]

/ulf,

Sub KeepLowestRow()
Dim myRow As Long

myRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("C1").EntireColumn.Insert Shift:=xlToRight
Range("C1:C" & myRow).FormulaR1C1 = "=COUNTIF(R1C2:RC[-1],RC[-1])"
Range("C1:C" & myRow).AutoFilter Field:=1, Criteria1:="<>1"
Range("C2:C" & myRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("C2:C" & myRow).AutoFilter
Range("C2").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Beautiful! Simple, fast, easy to adapt..
Thank you Bernie!
/ulf

/ulf,

Sub KeepLowestRow()
Dim myRow As Long

myRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("C1").EntireColumn.Insert Shift:=xlToRight
Range("C1:C" & myRow).FormulaR1C1 = "=COUNTIF(R1C2:RC[-1],RC[-1])"
Range("C1:C" & myRow).AutoFilter Field:=1, Criteria1:="<>1"
Range("C2:C" & myRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("C2:C" & myRow).AutoFilter
Range("C2").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP

Sorry - let me try to explain more carefully_

- I need to keep rows with lowest row number after sorting on B and M
(sometimes another cols) and delete possible other rows with same id. Deleted
rows should not leave empty rows.

- processing is automated - sub should be called from other sub with no user
involvment

thank you!
/ulf