Auto Sort problem

[JohnRooney]

Hi all

I have a fairly basic diary system set up. The data is sorted using the following code.(column D is a date)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
    With Range("A1:D" & Range("D65536").End(xlUp).Row)
        .Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
    End If
End Sub

It works fine, until I want to clear one of the rows of data. It will sort but leaves the blank row at the top of the sheet. Which I dont want.Is the anyway for the sheet to sort including any blank fields so that the row containing the earliest date is at the top of the list?Any help greatfully accepted.Thanks John.

[JBeaucaire]

I'll take a look. Meanwhile, please change the QUOTE tags above to CODE tags.

[JBeaucaire]

Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    For Each cell In Target
        If cell.Column = 4 Then
            With Range("A1:D" & Range("D" & Rows.Count).End(xlUp).Row)
                .Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _
                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            End With
        End If
    Next cell

End Sub

(Click on EDIT in post #1 to edit the quote tags into code tags)

[JohnRooney]

Many thanks- that sorted it- Brilliant! Apologies over the code/quote error.

I love this place

[JBeaucaire]

John, crossposting in multiple forums without including links in your posts here is against this forums rules.

http://www.mrexcel.com/forum/showthread.php?t=452859

This is a great way to waste people's time, something I'm sure you don't want to do.

I'm glad we got you worked out here, but please read through the Forum Rules so you can use and follow them effectively. They will only make your use of this resource better.

Thanks, Jerry.