I have an Excel file which is sorted ascending by column one. I would like
to write a macro to verify that, and if I find a row which is out of order I
would like to delete that row and all subsequent rows. How could I do that?

Dim rng as Range, cell as Range
set rng = Cells(rows.count,1).End(xlup)
set cell = range("A2")
do while cell.value < ""
if cell.Value < cell.offset(-1,0).Value then
range(cells(cell,rng).EntireRow.Delete
exit do
end if
set cell = cell.offset(1,0)
Loop

--
Regards,
Tom Ogilvy

"jerry chapman" wrote in message
...
I have an Excel file which is sorted ascending by column one. I would like
to write a macro to verify that, and if I find a row which is out of order
I
would like to delete that row and all subsequent rows. How could I do
that?

I changed -- range(cells(cell,rng).EntireRow.Delete
to -- range(cells(cell,rng).EntireRow).Delete and I got an
error on the first out of order row. It appears that this code would only
delete the first out of order row (when it works). I would like to delete
all rows after the first out of order row. Presumably I could do that by
looping through the rest of the row and using the .Delete command (when it
is fixed);

"Tom Ogilvy" wrote in message
...
Dim rng as Range, cell as Range
set rng = Cells(rows.count,1).End(xlup)
set cell = range("A2")
do while cell.value < ""
if cell.Value < cell.offset(-1,0).Value then
range(cells(cell,rng).EntireRow.Delete
exit do
end if
set cell = cell.offset(1,0)
Loop

--
Regards,
Tom Ogilvy

"jerry chapman" wrote in message
...
I have an Excel file which is sorted ascending by column one. I would
like
to write a macro to verify that, and if I find a row which is out of
order
I
would like to delete that row and all subsequent rows. How could I do
that?

range(cells(cell,rng).EntireRow.Delete
should be

range(cell,rng).EntireRow.Delete

Not sure how the extra cells got in there.

You assessment is incorrect. It should do what you described.

This version with the revised line, worked fine for me:

Sub DeleteRows()
Dim rng As Range, cell As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)
Set cell = Range("A2")
Do While cell.Value < ""
If cell.Value < cell.Offset(-1, 0).Value Then
Range(cell, rng).EntireRow.Delete
Exit Do
End If
Set cell = cell.Offset(1, 0)
Loop

End Sub

--
Regards,
Tom Ogilvy

"jerry chapman" wrote in message
...

I changed -- range(cells(cell,rng).EntireRow.Delete
to -- range(cells(cell,rng).EntireRow).Delete and I got an
error on the first out of order row. It appears that this code would only
delete the first out of order row (when it works). I would like to delete
all rows after the first out of order row. Presumably I could do that by
looping through the rest of the row and using the .Delete command (when it
is fixed);

"Tom Ogilvy" wrote in message
...
Dim rng as Range, cell as Range
set rng = Cells(rows.count,1).End(xlup)
set cell = range("A2")
do while cell.value < ""
if cell.Value < cell.offset(-1,0).Value then
range(cells(cell,rng).EntireRow.Delete
exit do
end if
set cell = cell.offset(1,0)
Loop

--
Regards,
Tom Ogilvy

"jerry chapman" wrote in message
...
I have an Excel file which is sorted ascending by column one. I would
like
to write a macro to verify that, and if I find a row which is out of
order
I
would like to delete that row and all subsequent rows. How could I do
that?

Thanks, that worked perfectly!
"Tom Ogilvy" wrote in message
...
range(cells(cell,rng).EntireRow.Delete
should be

range(cell,rng).EntireRow.Delete

Not sure how the extra cells got in there.

You assessment is incorrect. It should do what you described.

This version with the revised line, worked fine for me:

Sub DeleteRows()
Dim rng As Range, cell As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)
Set cell = Range("A2")
Do While cell.Value < ""
If cell.Value < cell.Offset(-1, 0).Value Then
Range(cell, rng).EntireRow.Delete
Exit Do
End If
Set cell = cell.Offset(1, 0)
Loop

End Sub

--
Regards,
Tom Ogilvy

"jerry chapman" wrote in message
...

I changed -- range(cells(cell,rng).EntireRow.Delete
to -- range(cells(cell,rng).EntireRow).Delete and I got an
error on the first out of order row. It appears that this code would
only
delete the first out of order row (when it works). I would like to
delete
all rows after the first out of order row. Presumably I could do that by
looping through the rest of the row and using the .Delete command (when
it
is fixed);

"Tom Ogilvy" wrote in message
...
Dim rng as Range, cell as Range
set rng = Cells(rows.count,1).End(xlup)
set cell = range("A2")
do while cell.value < ""
if cell.Value < cell.offset(-1,0).Value then
range(cells(cell,rng).EntireRow.Delete
exit do
end if
set cell = cell.offset(1,0)
Loop

--
Regards,
Tom Ogilvy

"jerry chapman" wrote in message
...
I have an Excel file which is sorted ascending by column one. I
would
like
to write a macro to verify that, and if I find a row which is out of
order
I
would like to delete that row and all subsequent rows. How could I
do
that?

try this change:
range(cell,rng).EntireRow.Delete

In fact, for testing, I like this:
range(cell,rng).EntireRow.Select

When it works, change .select to .delete



jerry chapman wrote:

I changed -- range(cells(cell,rng).EntireRow.Delete
to -- range(cells(cell,rng).EntireRow).Delete and I got an
error on the first out of order row. It appears that this code would only
delete the first out of order row (when it works). I would like to delete
all rows after the first out of order row. Presumably I could do that by
looping through the rest of the row and using the .Delete command (when it
is fixed);

"Tom Ogilvy" wrote in message
...
Dim rng as Range, cell as Range
set rng = Cells(rows.count,1).End(xlup)
set cell = range("A2")
do while cell.value < ""
if cell.Value < cell.offset(-1,0).Value then
range(cells(cell,rng).EntireRow.Delete
exit do
end if
set cell = cell.offset(1,0)
Loop

--
Regards,
Tom Ogilvy

"jerry chapman" wrote in message
...
I have an Excel file which is sorted ascending by column one. I would
like
to write a macro to verify that, and if I find a row which is out of
order
I
would like to delete that row and all subsequent rows. How could I do
that?





--

Dave Peterson

Hi,

Sample data in Activesheet
--Row-------A-----------------B-----------------------------
1 Number <== Label
2 1 <==First number
3 2
4 3
.. .
.. .
.. .
29995 29994
29996 29995
29997 1000 <== out of order
29998 29997
29999 29998
30000 29999
30001 30000
------------------------------------------------------------


Try the following code:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub TEST()

Dim R As Long
Dim N As Double

R = Cells(Rows.Count, 1).End(xlUp).Row
If R < 3 Then GoTo e:
N = Evaluate("MIN(IF(A2:A" & (R - 1) & "A3:A" & _
R & ",ROW(A3:A" & R & "),""""))")
If N = 0 Then GoTo e:
Range("A" & R & ":A" & N).EntireRow.Delete
e:

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--