Loops etc.
 

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

Loops etc.
 

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


"Jonathan Vickers" wrote in message
...
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

Loops etc.
 

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

--
Olly


"Jonathan Vickers" wrote in message
...
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

Loops etc.
 

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



--
Regards
Frank Kabel
Frankfurt, Germany

Jonathan Vickers wrote:
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

Loops etc.
 

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


"Jonathan Vickers" wrote in message
...
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

Loops etc.
 

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 Vickers" wrote in message
...
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

Loops etc.
 

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


"Jonathan Vickers" wrote in message
...
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 Vickers" wrote in message
...
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