How to get 'auto sort' (by Alf) to work in my worksheet

[nje]

I have attached my sample workbook.
I have unsuccesfully tried to adapt a code that was created and provided on this forum by Alf for someone else and it is just what I need.
I got it working in a new sheet, but when I copied it to where I needed it, it won't work.
There is another code on the same sheet so is it a possiblity that there is some sort of conflict?
I would appreciate it if someone could take a look and advise me please
What I'm trying to achieve, is after deleting the contents of out of date rows between A6 and G14, Alfs code sorts the remaining contents into date order.
Thanks

[Jakobshavn]

Hi Neil:

I have attached a version that works. The problem was with the sorting. Because you have two tables, one under the other, you can't use the:

.End(XLuP).Row

technique to define the bottom of the range.

You still need to do more work to come up with a more general solution.

[nje]

Thanks Jakobshavn, very well done, it works well, but only when the sheet is unprotected, is it possible for it to work when it is protected.
The sheet has to be protected to safeguard all the formulas in the rest of the sheet.
Is that what you were referring to with "You still need to do more work to come up with a more general solution" or is there some other problem I have overlooked
Thanks again

[Jakobshavn]

There are two separate items at issue

1. The sheet protection issue
2. The sort range issue

The first issue is the easy one. The event macro should:

• unprotect the worksheet
• perform the sort
• re-protect the worksheet

The second issue is that in my version only a fixed block of cells is being sorted (A6:G14). So we only go down to row #14. If there are more rows, we need to find that last row.

[nje]

Thank you for explaining that, although I don't know how to fix the protection issue. The cells to be sorted are just the cells within A6:G14 All the other rows below that, need to be left untouched.
Thanks again

[Jakobshavn]

Try this out:

[nje]

Brilliant, Thank you very much

[Jakobshavn]

Update this post if you require further assistance.

Have A Great Day!!

[nje]

I hate to do this to you, but would you mind looking at the attached file please?
I have transferred your code and the others to my workbook and everything seemed to be working ok, Your code was brilliant, the hide/show worked and so did the 'to the last row button'
But for some reason, now after I click on the 'clear expired rows' which as you know also sorts the dates, my hide show button throws up a fault and won't work
I've obviously done something wrong when putting codes together and it seems to have created a conflict, somewhere along the line. I'm a bit stuck now so would appreciate your help. The problems are on sheet 4 and 5
Thanks
p.s. I also added another code so that the users don't have to enter a colon when entering the time, so this might also have an impact.

[Jakobshavn]

Easy...............Once you run ClearCells, the sheet is protected...........therefore:

Public Sub HideShowRow()
ActiveSheet.Unprotect ("hollies")
     ActiveSheet.Rows("4:14").EntireRow.Hidden = Not (ActiveSheet.Rows("4:14").EntireRow.Hidden)
ActiveSheet.Protect ("hollies")
End Sub

[nje]

Brilliant, "Easy..." not for everyone. many thanks once again for coming to my rescue.
Unfortunately I can't 'star' you again it won't allow it. I'll hopfully leave you in peace now

[Jakobshavn]

I have no need to be "left in peace". There are some interesting flaws in the original approach that I may use for tutorial purposes.

[nje]

In that case then, and in the interests of my education and those you tutor, could you tell me why yet another problem has arisen? Is it code overload?
I have a code that was working, which allows you to leave out the colon when putting the time in sheets 4 and 5. That is now throwing up an error,
I can revert back to the colon method, but if it can easily be fixed I'd like to know how. Bye the way, I have made good use of your advice by teaching a friend who has a large 'work production' spreadsheet he uses at work. it consists of about 30 columns and I got it to work for him. He was impressed, I was amazed
Thanks again

[Jakobshavn]

In both sheets 4 & 5, try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("F6:G1000")) Is Nothing Then Exit Sub
     
     With Target
         vVal = Format(.Value, "0000")
          If IsNumeric(vVal) And Len(vVal) = 4 Then
            Application.EnableEvents = False
                .Value = TimeValue(Left(vVal, 2) & ":" & Right(vVal, 2))
                .NumberFormat = "[h]:mm"
            Application.EnableEvents = True
          End If
    End With
End Sub

[nje]

Thanks for trying but , unfortunately no change. You don't need to waste too much time on this, I can live with putting the colon in, It's the same wheteher the sheet is protected or unprotected, however, it will be protected when needed

[nje]

If it's any help, it says runtime error 1004, then debug highlights .numberformat="[H]:mm"

[Jakobshavn]

Lets finish this.

Post your latest version.

[nje]

I apologise for putting you through this, here is the latest version with your updated code in it.
Thank you for your perserverance

[Jakobshavn]

Try this one

[nje]

Perfect, many thanks. Can you tell what the @ means in "h:mm;@" Does it relate to the formating of the cells?
Thank you for the hard work you have put into for no gain, It may not seem like it but I've learnt quite a lot from this and I really appreciate it.

[Jakobshavn]

I copied the format from a working cell.