Find content in column and sort in specific order

Andy15

Board Regular
Joined
Apr 1, 2017
Messages
56
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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]score1[/TD]
[TD]score2[/TD]
[TD]score3[/TD]
[TD]score4[/TD]
[TD]score5[/TD]
[TD]score6[/TD]
[TD]score7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]red[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]blu[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]grn[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]ora[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]bla[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]tom[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ann[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]alan[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ellie[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]mary[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dave[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]lee[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ann[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]mary[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ellie[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]alan[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]tom[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]lee[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dave[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Last edited:
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

Code:
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
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
Code:
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. :)
 
Upvote 0

Similar threads

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top