Loops etc.

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
 
C

Chip Pearson

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
 
O

Olly

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
 
F

Frank Kabel

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
 
T

Trevor Shuttleworth

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
 
J

Jonathan Vickers

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
 
T

Trevor Shuttleworth

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
 
Top