VBA Autosort Exclude Text Value

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to automatically sort a range using the 'before close' declaration.

[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Input").Protect "password", UserInterFaceOnly:=True
With ThisWorkbook.Worksheets("Input")

If .Range("B7").Value = "" Then Exit Sub
.Range("B7:AH400").Sort Key1:=.Range("B7"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub[/vba]
The problem I'm having is that this currently sorts all the values in column "B" including pre-defined text I set for the user to tell them where to start entering a new row of data.

Could someone perhaps tell me please how I could exclude the text value "Enter your name" from the sort which takes place?

Many thanks and kind regards

Chris

[Kenneth Hobs]

Sounds like a nightmare to me. Post an example workbook with sheets showing before sort and manually marked up after sort sheet.

[hobbiton73]

Hi @Kenneth Hobs, thank you very much for taking the time to reply to my post.

I just wanted to let you know that I hadn't forgotten your kind offer of help. I've just got a bit of a crisis at the moment with the file in question which I'm desparately trying to fix.

I just wanted to let you know I should be able to post a file over the weekend. I hope this is ok/

I'm sorry for any inconvenienne this may cause.

Many thanks and kind regards

Chris

[hobbiton73]

Hi @kenneth Hobs, I hope you are well.

As promised, please find attached the file you asked for.

If you open this, and add the following values to column B you'll see the sort issue I have.

'Chris'
'Bob'
'Helen'

Many thanks and kind regards

Chris