Delete Duplicate Row above

[lapta301]

Dear All

I wonder if it is possible to help me out with a mighty labourious task.

What I would like is for a piece of code to check if the row that the cursor is currently in, is absolutely identical to the row above.
If it is then a message box connfirms this and gives the option to delete the row above. If they are not identical then a message will say so and obviously have no deletion option.

If possible I would prefer to not actually code in the extent of the columns but if it makes the task easier and more importantly do-able then including blank columns of which there are some then it is columns C to W

I hope that this makes sense and as ever your help is very much appreciated.

 

[lapta301]

Can anyone help?

 

[sagyer]

Here you go.. The below code gives an option to delete the row if it is duplicate :

Sub deleteDuplicate()
'
' deleteDuplicate Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'





Dim lastCurrRowCell, lastPrevRowCell As Range


Set lastCurrRowCell = ActiveSheet.Cells(ActiveCell.Row, 16384)
Set lastPrevRowCell = ActiveSheet.Cells(ActiveCell.Row - 1, 16384)


Dim duplicate As Boolean
duplicate = True


MsgBox lastCurrRowCell.End(xlToLeft).Column & " " & lastPrevRowCell.End(xlToLeft).Column


Dim i As Integer
    
    If lastCurrRowCell.End(xlToLeft).Column = lastPrevRowCell.End(xlToLeft).Column Then
    
        For i = 1 To lastCurrRowCell.End(xlToLeft).Column


            If Not (Cells(ActiveCell.Row, i).Value = Cells(ActiveCell.Row - 1, i).Value) Then
                duplicate = False
                Exit For
            End If
        
        Next
    Else
        
        duplicate = False
    
    End If


    If duplicate = True Then
        delQ = MsgBox("Duplicate Rows. Delete row?", vbOKCancel, "Delete Duplicate Row?")
            If delQ = 1 Then
                ActiveCell.EntireRow.Delete
            End If
    Else
        MsgBox "The rows are not duplicate"
    End If


End Sub

Hope it helps

Regards,

Sagar Yerunkar

 

[lapta301]

Sagar

Thank you so much for taking an interest.

The code seems to work brilliantly and will save me hours going many hundreds of possible duplicate rows.

Again my very sincere thanks for help.

Kind regards


Steve

 

[lapta301]

Sagar

Would you believe it but I have just found another spreasheet where I need to check many rows of data but in this case because there is a running total of values the last column to be compared is always L.

Could I please trouble you to make an adjustment to your brilliant code.

Thank you and my apologies for troubling you again.

Steve

 

[sagyer]

*deleted post*

 

[sagyer]

Sagar

Would you believe it but I have just found another spreasheet where I need to check many rows of data but in this case because there is a running total of values the last column to be compared is always L.

Could I please trouble you to make an adjustment to your brilliant code.

Thank you and my apologies for troubling you again.

Steve

Do you need to check only totals and if totals are the same then delete the row? If yes, here is the code :

    If Cells(ActiveCell.Row, 12).Value = Cells(ActiveCell.Row - 1, 12).Value Then '12 corresponds to 'L'
        delQ = MsgBox("Duplicate Rows. Delete row?", vbOKCancel, "Delete Duplicate Row?")
            If delQ = 1 Then
                ActiveCell.EntireRow.Delete
            End If
    Else
        MsgBox "The rows are not duplicate"
    End If

Regards,

Sagar Yerunkar

 

[Peter_SSs]

lapta301
1. What version of Excel are you using?

2. If a duplicate rows are found, you asked for the top row to be deleted. If they are identical, does it matter if the lower on is deleted instead?

3. Are there formulas in your sheet, or just fixed data? If formulas, what columns contain formulas?

4. Can you confirm that you need to check columns A:L to determine if rows are duplicates? (A brief explanation of what is beyond column L might also help.)

 

[lapta301]

Sagar

I want to ignore column L which are running totals.

So in this particular case I just want to check that columns A to M are identical.

Again my thanks for showing an interest.

Steve

 

[lapta301]

Peter

Sagar has dealt succesfully with my initial request but my thanks for having a look.

1. Excel 2010

2. You're quite right it doesn't matter at all which one is deleted, not sure why I was specific about that.

3. In the original case they were all fixed data.

4. There was nothing past column L, but in the second request column M is the last col and contains a running total based on a value elsewhere so I only want to be ceratin that the data is the same up to and including col L

Steve