Delete Duplicate Row above

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
997
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

sagyer

New Member
Joined
Feb 5, 2013
Messages
21
Here you go.. The below code gives an option to delete the row if it is duplicate :
Code:
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

Well-known Member
Joined
Nov 12, 2004
Messages
997
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 12, 2004
Messages
997
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 5, 2013
Messages
21

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,213
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 12, 2004
Messages
997
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 12, 2004
Messages
997
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,529
Messages
5,659,343
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top