macro to sort by multiple columns with no header, on a dynamic range selected using an input box and no header

[Rackette]

Hi, and thank you for any help and assistance.
My spreadsheet has 5 columns and about 200 rows. One of the columns is Name, then Start time, another is end time, a 4th is location, a 5th is role.
Initially, I sort by start time and then every time there is a change in start time, I have to insert a blank row so that that change is more noticeable to those looking at the printout...which means I can't us a header row when sorting.

What I need help with is:
I need to use an input box to select the range where the 'start time' = 1300.
Then, within that input box range, I need to sort by 'location' and then 'role', and then by 'name'.
So that the 1300 start time range gets sorted alphabetically by location, and within each different location, it is sorted alphabetically by role, and then, within each role, sorted alphabetically by name.

This is what it starts as:

Already sorted by start time and then alphabetically by name; blank row between each change in start time.
For some reason there is a HUGE gap between the bottom of this grid and the start of the next one...please scroll.

​

[TABLE="class: grid, width: 15, align: left"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[TD]Role[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]1000[/TD]
[TD]1100[/TD]
[TD]Annex[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1000[/TD]
[TD]1100[/TD]
[TD]Annex[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]1000[/TD]
[TD]1115[/TD]
[TD]Bridge[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]Peggy[/TD]
[TD]1000[/TD]
[TD]1115[/TD]
[TD]Shed[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Shed[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]Audrey[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Bridge[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]Hermi[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Annex[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Bridge[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Marcel[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Annex[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]Phillip[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Annex[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Garth[/TD]
[TD]1315[/TD]
[TD]1400[/TD]
[TD]Annex[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]Mable[/TD]
[TD]1315[/TD]
[TD]1400[/TD]
[TD]Shed[/TD]
[TD]d[/TD]
[/TR]
</tbody>[/TABLE]

























I need to make changes to the 1300 'start time' block but all the other ranges on the sheet I DON'T want to make any changes to.
The 1300 'selected-by-an-input-box' range needs to end up like this after running the macro:

[TABLE="class: grid, width: 20, align: left"]
<tbody>[TR]
[TD]Hermi[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Annex[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Phillip[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Annex[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Marcel[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Annex[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]Henry[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Bridge[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Audrey[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Bridge[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD]1300[/TD]
[TD]1330[/TD]
[TD]Shed[/TD]
[TD]b[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-
NB:- If you are always going to search by 1300 you don't really need an input box !!!

[COLOR="Navy"]Sub[/COLOR] MG05Jul28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, MyNum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
MyNum = InputBox("Enter number", "Number", "Type your number here")
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Dn(1).Offset(, 1) = MyNum [COLOR="Navy"]Then[/COLOR]
            Dn.Resize(, 5).Sort Key1:=Dn(1).Offset(, 3), Order1:=xlAscending, _
            Key2:=Dn(1).Offset(, 4), Order2:=xlAscending, _
            Key3:=Dn(1), Order3:=xlAscending, _
            Header:=xlNo
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn

Regards Mick

 

[Rackette]

I appreciate you taking the time to help me, MickG.
When I tried to run it, it said it needed an 'end sub', so I stuck one in down below the "Next Dn".
But when I run it on some data, nothing gets sorted.
When the input box pops up, I enter my data range which was A8:D11.
Is there a problem if there are blank rows between the different 'start times'?
Thank you!
-Christine

 

[MickG]

Sorry abiut the "End Sub"
Try this Example file.
NB :- Enter Number 1300 in Inbox.
NB:- The code needs the blank rows between each set of data to work correctly.
https://app.box.com/s/ahn6bpqpp7viuurpzfho

 

[Rackette]

It works much better when *I* do what I'm supposed to.
What you guys do by taking time to help on here makes SO much difference for those of us who don't know how to do it.
Thank you for taking the time to do what you do here.

 

[MickG]

You're very welcome, and thank you for your kind remarks.