Move Certain Rows to the Bottom 1

[A1E5D4]

I have a repeating special sorting job to do: I have to sort a sheet by Column B and then to move rows with a certain key word in column B to the bottom. For instance I have "PO", "MB" and these should be placed at the bottom. The overall sorting should be asending and the same with the rows with key words. Thanks.

 

[TonyJollans]

Hi A1E5D4,

Can you create another column containing =IF(ISERR(FIND(B1,"PO, MB, etc.&quot),"",B1) (copied down as far as your data) and sort on that first?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[A1E5D4]

Thanks, Tony. I wrote a piece of sub which solves my problem. Here is the code I would like to share with all.

Sub MoveRowstoBottom(TargetSheet As String, TargetColumn As Byte, KeyWord As String)
Dim nRow As Long
Dim nLastRow As Long
Dim sRowsToMove As String

nLastRow = Worksheets(TargetSheet).UsedRange.Rows.Count
For nRow = nLastRow To 1 Step -1
If Cells(nRow, TargetColumn) = KeyWord Then
sRowsToMove = nRow & ":" & nRow
Range(sRowsToMove).EntireRow.Copy
ActiveSheet.Paste Rows(nLastRow + 1)
Range(sRowsToMove).EntireRow.Delete
End If
Next nRow

End Sub

 

[SkipVought]

A1E5D4,

Unless you have a burning desire to write code for every simple solution, I like Tony's solution better. I try to stay away from copy and paste solutions whenever I can.

If you must code, then some code to do what Tony's spreadsheet solution does, by harcoding a value rather than a formula and sorting would be the second best, in my opinion.

Tony, you beat me to "my solution" ==> *

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884