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]

Oops what an idiot

Typo I should have said that cols A to L need to be checked that they are identical and to ignore the last column M


Steve

 

[Peter_SSs]

For your second request, try this in a copy of your workbook.

Sub CheckDuplicate_2()
  Dim a
  Dim i As Long, r As Long
  
  Const ColsToCheck As Long = 12 '<- That is, columns A:L
  
  r = ActiveCell.Row
  a = Cells(r - 1, 1).Resize(2, ColsToCheck + 1).Value
  i = 1
  Do Until a(1, i) <> a(2, i) Or i > ColsToCheck
    i = i + 1
  Loop
  If i > ColsToCheck Then
    If MsgBox("Row " & r & " is a duplicate of row " & r - 1 & vbLf _
        & "Delete row " & r & "?", vbYesNo) = vbYes Then
      Rows(r).Delete
    End If
  Else
    MsgBox "Row " & r & " is not a duplicate of row " & r - 1, vbOKOnly
  End If
End Sub

For an alternative for your original request, you could try replacing this bit of my code above

Sub CheckDuplicate_2()
  Dim a
  Dim i As Long, r As Long
  
  Const ColsToCheck As Long = 12 '<- That is, columns A:L

with this

Sub CheckDuplicate_1()
  Dim a
  Dim i As Long, r As Long, ColsToCheck As Long
  
  ColsToCheck = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column

 

[sagyer]

*Deleted*

 

[lapta301]

Peter

My apologies for the delay in replying as I have been away for a few days.

Works fine thank you so much.

As I plan to use this frequently in various worksheets would it be possible to show on the first line of the message box the end column letter that is being used in the comparison.

In any event thank you so much for taking an interest in my request.

Regards

Steve

 

[Peter_SSs]

As I plan to use this frequently in various worksheets would it be possible to show on the first line of the message box the end column letter that is being used in the comparison.

See if replacing the message box section with this is what you mean.

If i > ColsToCheck Then
  If MsgBox("Row " & r & " is a duplicate of row " & r - 1 & vbLf & _
      "from column A to column " & Replace(Cells(1, ColsToCheck).Address(0, 0), 1, "") _
      & vbLf & "Delete row " & r & "?", vbYesNo) = vbYes Then
    Rows(r).Delete
  End If
Else
  MsgBox "Row " & r & " is not a duplicate of row " & r - 1 & vbLf & _
    "from column A to column " & Replace(Cells(1, ColsToCheck).Address(0, 0), 1, ""), vbOKOnly
End If

 

[lapta301]

Peter

That's very good thank you.

Sincere thanks for taking the time to help me out

Regards

From a chilly Southern England