Loops etc.

  • Thread starter Thread starter Jonathan Vickers
  • Start date Start date
J

Jonathan Vickers

Hi,

I'm trying to get a macro to compare dates in successive rows and then
delete the date in the lower row (i.e. with higher row number) if it is the
same as the date above, but I can't seem to get it to work. My plan was:

For i To BottomRow
If value in (B,i) = value in (B,i-1)
Then delete value in (B,i)

However I can't seem to get this to work.

Any help would be much appreciated,

Jon
 
Jon,

Whe you are going to be deleting rows in a loop, you should loop
from the bottom upwards. Otherwise, you'll miss rows. For
example,

For i = LastRow To 1 Step -1
' your code to delete
Next i


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
This works, but only compares 2 rows.

Dim i As Integer

For i = startrow To endrow
If Cells(i + 1, 1).Value = Cells(i, 1).Value Then
Rows(i + 1).EntireRow.Delete
End If
Next i
 
Hi Jonathan
try the following

Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).row
For RowNdx = LastRow-1 To 1 Step -1
If Cells(RowNdx, "B").Value = cells(RowNdx+1,"B").value Then
Rows(RowNdx+1).Delete
Exit For
End If
Next RowNdx
End Sub
 
Jonathan

it is usually better to work from the bottom up. If you work from the top
down, when you delete a row you effectively jump over the next row so your
row counter gets screwed.

For i = BottomRow To 1 Step -1

Regards

Trevor
 
Thanks for all the help guys. I actually just want to delete the value in
the cell, rather than the whole row, but I think I should be able to work
out how to do that!

Thanks again,

Jon
 
Jonathan

sorry, obviously over-engineered !

Try this:

Dim BottomRow As Long
Dim i As Long

BottomRow = Range("B65536").End(xlUp).Row

For i = BottomRow To 2 Step -1
If Range("B" & i).Value = _
Range("B" & i - 1).Value Then
Range("B" & i).ClearContents
End If
Next 'i

Regards

Trevor
 
Back
Top