Delete Rows

J

jerry chapman

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?
 
T

Tom Ogilvy

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
 
J

jerry chapman

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);
 
T

Tom Ogilvy

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
 
J

jerry chapman

Thanks, that worked perfectly!
Tom Ogilvy said:
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
Click to expand...
 
D

Dave Peterson

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
 
S

Soo Cheon Jheong

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
_ _
^¢¯^
--
 
Top