Sort when Worksheet_Change Does Nothing?!

[WpgExcel]

I have a user form set up to add a new part # to the worksheet, gather some data then create a new row adding that data once you click 'save' and that works fine but once the new row is added I want the sheet to automatically sort Ascending on column C...I've tried many different things and can't get it to work, below is the code I have now and it does nothing...


Private Sub Revised_Change(ByVal Target As Range)


Range("C1").CurrentRegion.Sort _
Key1:=Range("C1"), _
Order1:=xlAscending, _
Header:=xlYes


End Sub

Any suggestions are appreciated

 

[WpgExcel]

It seems that with your code the sheet 'remembers' if there was data in a row previously even if its been deleted and that's how the data is ending up way down in row 2003...

 

[shg]

It doesn't matter if the sort range extends below the used range -- blank rows always end up at the bottom.

 

[WpgExcel]

The next blank row is 523, this is skipping a bunch of blanks and putting the data in 2003...it also leaves blanks in between data even if its deleted. For example it added data to row 2003, I deleted that and then tried the form again and the next time it put the data into 2004

 

[shg]

If you go all the way to to the bottom of col B and do Ctrl + Up Arrow, where do you land?

 

[WpgExcel]

Row 525 now with two blanks above it...I right click 'delete rows' down to 2000 which made it stop going that far, but now as I retest and delete data from cells it skips those blank rows unless I right click and 'delete row'

 

[shg]

There's some residual something in there.

You might try

.Range("C1").CurrentRegion.EntireColumn.Sort ...

 

[WpgExcel]

Thanks very much for your help, this is what ended up working...

Private Sub SAVE_Click()


'Part Num is mandatory
If Len(Trim(NewPartFrm.PrimeNum.Value)) = 0 Then
MsgBox "Part Number is Mandatory"
Exit Sub
End If


'Description is mandatory
If Len(Trim(NewPartFrm.PDesc.Value)) = 0 Then
MsgBox "Part Description is Mandatory"
Exit Sub
End If


'TypeCode is mandatory
If Len(Trim(NewPartFrm.TypeCode.Value)) = 0 Then
MsgBox "Part Number is Mandatory"
Exit Sub
End If


Dim emptyRow As Long


'Make Sheet2 active
Sheet2.Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1


'Transfer information
Cells(emptyRow, 1).Value = PrimeNum.Value
Cells(emptyRow, 2).Value = PDesc.Value
Cells(emptyRow, 3).Value = TypeCode.Value
Cells(emptyRow, 5).Value = SubCode.Value
Cells(emptyRow, 7).Value = Mfr.Value
Cells(emptyRow, 8).Value = MfrNum.Value
Cells(emptyRow, 9).Value = UOMCombo.Value
Cells(emptyRow, 10).Value = MinQty.Value
Cells(emptyRow, 12).Value = PrefSup.Value
Cells(emptyRow, 13).Value = Cost.Value
Cells(emptyRow, 14).Value = Altsup.Value
Cells(emptyRow, 15).Value = Location.Value



Call Sheet2_Sort


End Sub


Private Sub Sheet2_Sort()


Range("A1").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes


Call UserForm_Initialize


End Sub