Delete Entire Row If Q
 

I am trying to find some code that will delete an entire row if certain
properties exist. I require to do this from the last populated row in my
worksheet up to row 1, as my entire worksheet is dynamic

An example of my layout is as shown below (I do not have a header row). I
have aleady sorted my range in A (ascending); then B (ascending); then Q
(descending)

3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300


As you can see I have several rows with the same dates, but only one date of
each is relevant in that it is the most up todate figure, which is all that
I require. I have a TimeStamp in Column Q which I have not shown above. The
logic of what I am trying to express in code is as follows

Delete the row if : A(Row number) = A(Row number-1) AND B(Row number) =
B(Row Number -1) AND Q(Row number) < Q(Row Number -1)

Otherwise

Move up to the Next Row until you reach Row A

Delete Entire Row If Q
 

Hi, You will need some VBA code to do this. First determine the last row of
the sheet, then work up from the last row to the first row checking the
condition to delete the entire row or not. e.g.......

Sub DeleteRws()
Dim xLastRow As Long, xRow As Long
With Sheets("Sheet1")
xLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If xLastRow 1 Then
For xRow = xLastRow To 2 Step -1
If .Cells(xRow, 1) = .Cells(xRow - 1, 1) And _
.Cells(xRow, 2) = .Cells(xRow - 1, 2) And _
.Cells(xRow, 17) < .Cells(xRow - 1, 17) Then
Rows(xRow).EntireRow.Delete
End If
Next xRow
End If
End With
End Sub


--
Cheers
Nigel



"John" wrote in message
...
I am trying to find some code that will delete an entire row if certain
properties exist. I require to do this from the last populated row in my
worksheet up to row 1, as my entire worksheet is dynamic

An example of my layout is as shown below (I do not have a header row). I
have aleady sorted my range in A (ascending); then B (ascending); then Q
(descending)

3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300


As you can see I have several rows with the same dates, but only one date
of
each is relevant in that it is the most up todate figure, which is all
that
I require. I have a TimeStamp in Column Q which I have not shown above.
The
logic of what I am trying to express in code is as follows

Delete the row if : A(Row number) = A(Row number-1) AND B(Row number) =
B(Row Number -1) AND Q(Row number) < Q(Row Number -1)

Otherwise

Move up to the Next Row until you reach Row A

Delete Entire Row If Q
 

try somthing like this

Sub deleterowifsame()
For i = 16 To 9 Step -1
If Cells(i - 1, "f") = Cells(i, "f") And _
Cells(i - 1, "g") = Cells(i, "g") And _
Cells(i - 1, "h") = Cells(i, "h") Then
Rows(i).Delete
End If
Next i
End Sub

--
Don Guillett
SalesAid Software

"John" wrote in message
...
I am trying to find some code that will delete an entire row if certain
properties exist. I require to do this from the last populated row in my
worksheet up to row 1, as my entire worksheet is dynamic

An example of my layout is as shown below (I do not have a header row). I
have aleady sorted my range in A (ascending); then B (ascending); then Q
(descending)

3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300


As you can see I have several rows with the same dates, but only one date
of each is relevant in that it is the most up todate figure, which is all
that I require. I have a TimeStamp in Column Q which I have not shown
above. The logic of what I am trying to express in code is as follows

Delete the row if : A(Row number) = A(Row number-1) AND B(Row number) =
B(Row Number -1) AND Q(Row number) < Q(Row Number -1)

Otherwise

Move up to the Next Row until you reach Row A

Delete Entire Row If Q
 

Thanks Nigel, appears to work good. One Q - doesn't your code determine the
last populated Row to start from?


"Nigel" wrote in message
...
Hi, You will need some VBA code to do this. First determine the last row
of
the sheet, then work up from the last row to the first row checking the
condition to delete the entire row or not. e.g.......

Sub DeleteRws()
Dim xLastRow As Long, xRow As Long
With Sheets("Sheet1")
xLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If xLastRow 1 Then
For xRow = xLastRow To 2 Step -1
If .Cells(xRow, 1) = .Cells(xRow - 1, 1) And _
.Cells(xRow, 2) = .Cells(xRow - 1, 2) And _
.Cells(xRow, 17) < .Cells(xRow - 1, 17) Then
Rows(xRow).EntireRow.Delete
End If
Next xRow
End If
End With
End Sub


--
Cheers
Nigel



"John" wrote in message
...
I am trying to find some code that will delete an entire row if certain
properties exist. I require to do this from the last populated row in my
worksheet up to row 1, as my entire worksheet is dynamic

An example of my layout is as shown below (I do not have a header row). I
have aleady sorted my range in A (ascending); then B (ascending); then Q
(descending)

3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300


As you can see I have several rows with the same dates, but only one date
of
each is relevant in that it is the most up todate figure, which is all
that
I require. I have a TimeStamp in Column Q which I have not shown above.
The
logic of what I am trying to express in code is as follows

Delete the row if : A(Row number) = A(Row number-1) AND B(Row number) =
B(Row Number -1) AND Q(Row number) < Q(Row Number -1)

Otherwise

Move up to the Next Row until you reach Row A

Delete Entire Row If Q
 

Hi John
The last populated row is determined by this piece of code in the procedure
supplied.........

xLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

It looks in column A to find last filled cell.

--
Cheers
Nigel



"John" wrote in message
...
Thanks Nigel, appears to work good. One Q - doesn't your code determine
the
last populated Row to start from?


"Nigel" wrote in message
...
Hi, You will need some VBA code to do this. First determine the last
row
of
the sheet, then work up from the last row to the first row checking the
condition to delete the entire row or not. e.g.......

Sub DeleteRws()
Dim xLastRow As Long, xRow As Long
With Sheets("Sheet1")
xLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If xLastRow 1 Then
For xRow = xLastRow To 2 Step -1
If .Cells(xRow, 1) = .Cells(xRow - 1, 1) And _
.Cells(xRow, 2) = .Cells(xRow - 1, 2) And _
.Cells(xRow, 17) < .Cells(xRow - 1, 17) Then
Rows(xRow).EntireRow.Delete
End If
Next xRow
End If
End With
End Sub


--
Cheers
Nigel



"John" wrote in message
...
I am trying to find some code that will delete an entire row if certain
properties exist. I require to do this from the last populated row in
my
worksheet up to row 1, as my entire worksheet is dynamic

An example of my layout is as shown below (I do not have a header row).
I
have aleady sorted my range in A (ascending); then B (ascending); then
Q
(descending)

3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 12/12/05 ?8,500
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 13/12/05 ?8,700
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300
3047 14/12/05 ?12,300


As you can see I have several rows with the same dates, but only one
date
of
each is relevant in that it is the most up todate figure, which is all
that
I require. I have a TimeStamp in Column Q which I have not shown above.
The
logic of what I am trying to express in code is as follows

Delete the row if : A(Row number) = A(Row number-1) AND B(Row number) =
B(Row Number -1) AND Q(Row number) < Q(Row Number -1)

Otherwise

Move up to the Next Row until you reach Row A