Delete Row Based On Cell Below

dodger7 · Jun 22nd 2007

G'Day folks

I currently have a spreadsheet with circa 50,000 lines with info regarding our websites at work.

In every row, column "A" contains a unique reference number.There eill either be 1 row for this reference number, or there will be 2 rows. there will never be more than 2 rows for each reference. I wanted some code to say that if theres only one row, then delete it but if the if the cell underneath it is the same then its ok.

I tried the following but it deletes the row anyway, supposing the cell under it is the same or not

Code

Sub test()


Dim icell
icell = ActiveCell.Offset(1, 0).Value


Range("A1").Select
Do
If ActiveCell.Value = icell Then
ActiveCell.Offset(2, 0).Select


Else
ActiveCell.Rows.Delete


End If
Loop Until ActiveCell.Value = ""
End Sub

Display More

Thanks very much for any assistance
Jamie

Brian Walters · Jun 22nd 2007

Re: Delete Row Based On Cell Below

Try this

Code

Sub test()


  Range("A65536").End(xlUp).Select
  Do Until ActiveCell.Value = "" Or ActiveCell.Row = 1
    If Not (ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Or ActiveCell.Value = ActiveCell.Offset(1, 0).Value) Then ActiveCell.Rows.Delete
    ActiveCell.Offset(-1, 0).Select
  Loop
End Sub

**StephenR** · Jun 22nd 2007

Re: Delete Row Based On Cell Below

Try this. Not sure why your code doesn't work, might be to do with how icell is set up at the start.

Code

Sub x()
Dim r As Long


For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(r, 1) <> Cells(r - 1, 1) Then
        Rows(r).Delete
    End If
Next r
    
End Sub

Display More

dodger7 · Jun 22nd 2007

Re: Delete Row Based On Cell Below

cheers guys, both seem to work perfectly

thanks again
Jamie

dodger7 · Jun 29th 2007

Re: Delete Row Based On Cell Below

Ive just realised when I run Brians code it only deletes the first cell, and not the row. Any ideas why this is?

Delete Row Based On Cell Below

Participate now!

Share