Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loops???? | Excel Worksheet Functions | |||
Loops | Excel Discussion (Misc queries) | |||
Using For - Next Loops in VB | New Users to Excel | |||
LOOPS | Excel Programming | |||
Loops | Excel Programming |