deleting a range after sorting
Posted by Mark Henderson on January 30, 2002 2:26 PM
OK, here is my dilemma. Lets say I have 3 columns of data, and I sort ascending on column 3, where there are only 2 possible entries - either Gore or Invercargill. After sorting, all Invercargill entries sit at the bottom of the list, and it is these entries I wish to remove completely. However, my data columns are in columns F, G, and H and I cannot simply remove the rows ro remove invervargill entries. I need to find a way that will scroll down column H (the column with Inverargill entered) until it finds the first Invercargill entry, then have it scroll to the last Invercargill entry, and finally delete this range. After that, it would also be nice to be able to have the macro automatically select and copy the range for all the remaining Gore entries.
Any help would be greatly appreciated.
adieu
Mark
Posted by Benny on January 30, 2002 3:02 PM
**** Here is some sample code that may be useful, and at the end it will select the whole contiguous region (you will need to change the starting point of this macro -- also, if there are blank cells in the data this will exit the loop).
Range("H1").Select
Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Select
If ActiveCell = "Invercargill" Then
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
Loop
Range("H1").Select
Selection.CurrentRegion.Select
Posted by Mark Henderson on January 30, 2002 3:27 PM
ok thank you. That works . I moved my data to a new worksheet and deleting rows is now effective. But I need to select all 3 columns at the end of the process..ie columns F, G, and H..not just H. Ideas?
