Inserting a row alphabetically

[Sheeny]

Hi

Im not quite sure what Im doing wrong...

Based on a value in a list box, I want to insert a row in the proper alphabetical order.

This is what I have:
Private Sub CmdUpdateNew_Click()

Sheet2.Activate
Sheet2.Range("A4").Select

For i = 4 To Range("A65536").End(xlUp).Row
If Range("A" & i).Value > ListNew.Value And Range("A" & i - 1).Value < ListNew.Value Then
Rows(i).EntireRow.Insert
Exit For
End If
Next


End Sub

Any help would be greatly appreciated!
Thanks!
Sheeny

 

[KenWright]

Just a guess here, but is your problem that it seems to miss rows? Try running upwards from the bottom as opposed to down, else you will change the number of rows that you started your count with.

Private Sub CmdUpdateNew_Click()

Sheet2.Activate
Sheet2.Range("A4").Select

LastRw = Cells(Rows.Count, "A").End(xlUp).Row

For i = LastRw to 4 Step -1
If Range("A" & i).Value > ListNew.Value And Range("A" & i - 1).Value < ListNew.Value Then
Rows(i).EntireRow.Insert
Exit For
End If
Next


End Sub

Regards
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Sheeny]

Thanks but no thats not quite what I need...

Is there a way I could add to the bottom of the list and then alphabetically sort?

Would the data in columns beside it be sorted with the data, or would they remain in hteir previous positions?

Im using Excel 2000.

Thanks.