Find content in column and sort in specific order

[Andy15]

Hi Guys

I am looking for some help with finding some content within a column and re-arranging the order of the found rows to a specific order.

To make it a bit clearer please see below

		score1	score2	score3	score4	score5	score6	score7
		text	text	text	text	text	text	text
123	red	number	number	number	number	number	number	number
124	blu	number	number	number	number	number	number	number
125	grn	number	number	number	number	number	number	number
126	ora	number	number	number	number	number	number	number
127	bla	number	number	number	number	number	number	number
	tom	code1	code1	code1	code1	code1	code1	code1
	ann	code2	code2	code2	code2	code2	code2	code2
	alan	code3	code3	code3	code3	code3	code3	code3
	ellie	code4	code4	code4	code4	code4	code4	code4
	mary	code5	code5	code5	code5	code5	code5	code5
	dave	code6	code6	code6	code6	code6	code6	code6
	lee	code7	code7	code7	code7	code7	code7	code7

<tbody>
</tbody>

The cells I need to reorder will always be in column 2 and will always be in the same order starting with "tom" and ending with "lee"

I would like to run a macro to find the relevant rows and all the relevant columns to the right and reorder them in the following order

	ann	code2	code2	code2	code2	code2	code2
	mary	code5	code5	code5	code5	code5	code5
	ellie	code4	code4	code4	code4	code4	code4
	alan	code3	code3	code3	code3	code3	code3
	tom	code1	code1	code1	code1	code1	code1
	lee	code7	code7	code7	code7	code7	code7
	dave	code6	code6	code6	code6	code6	code6

<tbody>
</tbody>

in the above examples the cells with code1, code2 are for example only, in reality they will be various numbers.

Hope this makes sense

Thanks for any help

 

[footoo]

Hi Footoo,

I am not sure why but on further testing, the code sorts as requested but when I try and save the workbook it gives an error Microsoft excel has stopped working.

I then have the options to check if there is solution or close the programme.

Thought it may help someone else that may try code.

Not sure if there is anything obvious why that would happen

Thanks
Andy

Sub FT()
Dim rng As Range, LstNum%
Set rng = [B:B].Find("tom").Resize(7)
LstNum = Application.CustomListCount + 2
Application.AddCustomList Array("ann", "mary", "ellie", "alan", "tom", "lee", "dave")
rng.EntireRow.Sort key1:=rng(1), order1:=xlAscending, Header:=xlNo, OrderCustom:=LstNum
[COLOR=#ff0000]ActiveSheet.Sort.SortFields.Clear[/COLOR]
LstNum = LstNum - 1
Application.DeleteCustomList LstNum
End Sub

 

[Peter_SSs]

Your assumptions were correct and my worksheet is as per sample, the code just seems to make the sheet flicker but nothing changes.

Then the cells in column A beside the names probably look clear but actually contain something. Out of interest, try running this code & tell us what the results are.

Sub CheckRows()
  MsgBox "'Tom' row: " & Columns("B").Find("tom").Row & vbLf & "Last row in col A with data: " & Cells(Rows.Count, 1).End(xlUp).Row
End Sub

I have tried your latest code and that is working brilliantly.

Many thanks for your help

Good news. You are very welcome.