Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
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 _ _ ^ąŻ^ -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |