VBA for deleting duplicate rows
 

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

VBA for deleting duplicate rows
 

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


"ulfb" wrote in message
...
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

VBA for deleting duplicate rows
 

On Jun 26, 11:06 am, ulfb wrote:
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

VBA for deleting duplicate rows
 

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" wrote:

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


"ulfb" wrote in message
...
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

VBA for deleting duplicate rows
 

/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.D elete
Range("C2:C" & myRow).AutoFilter
Range("C2").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"ulfb" wrote in message
...
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" wrote:

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


"ulfb" wrote in message
...
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