Assuming the term “current” is in a column of its own, and assuming there are no empty cells in that column, the process would be very simple.
Assume column B would have the values “current” and your say 5 columns with data are A to E. Code below will identify the cell, select the row, delete it and once it has finished deleting all the rows with “current” in them, get rid of the empty rows:
==========================================================
Range("B1").Select
Do Until Selection.Value = ""
If Selection.Value = "current" Then
Selection.Offset(0, -1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Selection.Offset(1, 0).Select
Selection.End(xlToLeft).Select
Selection.Offset(-1, 1).Select
End If
Selection.Offset(1, 0).Select
Loop
Columns("A:E").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
=============================================================
To suit your own sheet, you change the column “B1” with whichever column will have the “current”. You change the 4th line [Selection.Offset(0, -1).Select] to have the correct offset value to reach column “A”. i.e. if “current” is under column “C” then Offset(0, -2), if under “D” then Offset(0, -3) and so on.
Likewise, in the last line before “End if” [Selection.Offset(-1, 1).Select], the second offset value should be replaced by the positive value of the negative offset you have used in line 4. i.e.: for column C Offset(-1, 2), or for column D Offset(-1, 3), and so on.
Finally, when getting rid of the cleared rows, it will sort your sheet in “ascending” order (see where it says “Order1:=xlAscending” in the last two lines) using the column “A” as can be seen in “Selection.Sort Key1:=Range("A1"),”
You can change it to sort Descending or by a different column, by replacing the column letter “A” with the right column and by replacing Ascending with Descending.
