Help with a simple Code

[acrowe]

I am creating a simple sheet and want the data to automatically sort from the date in Column A. My problem is that it is sorting as soon as I put the date information in causing me to have to tab up to where it has been sorted to and finish inputting my other information. I need it to either wait until all the information is put into the row or after it sorts to automatically move to the resorted area for input of the rest of the information. I am using columns A - I presently. Below is the macro I am using. Any help would be appreciated. Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Application.EnableEvents = True
End Sub

 

[Scott Huish]

You didn't say how many columns you were using, this sample has it ending at Column D, and that every column must contain a value before it sorts, change as necessary:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range
Set d = Intersect(Target, Range("D:D"))
If d Is Nothing Then Exit Sub
If Application.WorksheetFunction.CountA(Range("A" & d.Row).Resize(, 4)) = 4 Then
    Application.EnableEvents = False
        Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    Application.EnableEvents = True
End If
End Sub

 

[acrowe]

Sorry about the lack of information. I am using to Column "I" presently.

 

[acrowe]

Maybe I'm doing something wrong but it is not sorting even to column "d" when I paste it in.

 

[Scott Huish]

Do all 4 columns have data?

 

[acrowe]

Yes I made sure I hade something in every column but I may still be doing something wrong.

 

[Scott Huish]

Was Column D included with the change when all columns had data?

For example, if you put something in Column D, then added the rest of the data, it wouldn't work, because my code is based on a change in Column D.

You could change this line:

Set d = Intersect(Target, Range("D:D"))

to

Set d = Intersect(Target, Range("A:D"))

to account for that.

 

[acrowe]

I start with column A and put data in every cell out to D. I can send you my complete file if you would like to see it? I do appreciate your help very much.

 

[madforgolf]

Hi, i tried this and it stopped working after 4 line, but if i pasted into the column (leaving a cell blank between data it worked fine.) jumping into the correct slot. If a cell is left blank it wont work, so i filled ' into the cell and no bother.

Its a great piece of code which i know i can use for work filling in data which will auto jump to the correct order, so thanks for posting.

Cheers

Marty