Advanced sorting in a spreadsheet

[Ian M]

In my already-populated Excel spreadhseet I want to:

- locate all the rows which contain the word "Robert" in Column "E"
(only column "E");

- cut out the entire row

- paste the entire row just after the end of the last row with data in
it.

- then remove the empty row from which they have been cut.

However, the number of rows in the spreadsheet varies from day to day,
so the last row with data in it cannot have an absolute value.

Any ideas?

Please advise.

paddymichelle

 

[Cecilkumara Fernando]

Ian M,
Try,
Sub Macro10()
LR = Range("E" & Rows.Count).End(xlUp).row
Range("A1:F" & LR).AutoFilter _
Field:=5, Criteria1:="Robert"
Range("E2:E" & LR) _
.SpecialCells(xlCellTypeVisible) _
.EntireRow.Copy _
Destination:=Range("A" & LR + 1)
Range("E2:E" & LR) _
.SpecialCells(xlCellTypeVisible) _
.EntireRow.Delete
Range("A1:F" & LR).AutoFilter
End Sub

Worked for me
Cecil