Inserting cells

[Tyrell]

I have a list that is organized alphabetically in a column. I want the user
to enter an item to add to the list in a text box, then click a button to
have the text box information added to my list. How do I INSERT the
information into the list in alphabetical order and keep all original list
data? Then, how would I go about deleting an item in the same manner?
--
Tyrell Fickenscher
Plant Manager / Agronomist

[Chip Pearson]

The simplest way would be to insert the new data at the bottom of
the list and resort.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tyrell" <Tyrell@discussions.microsoft.com> wrote in message
news:6138BF5A-B430-4896-B312-D4E47493020C@microsoft.com...
>I have a list that is organized alphabetically in a column. I
>want the user
> to enter an item to add to the list in a text box, then click a
> button to
> have the text box information added to my list. How do I
> INSERT the
> information into the list in alphabetical order and keep all
> original list
> data? Then, how would I go about deleting an item in the same
> manner?
> --
> Tyrell Fickenscher
> Plant Manager / Agronomist

[kounoike]

I also think Chip's way is the simplest.
this one is not simple way. besides, not case sensitive.
and the list must always be alphabetically ascending order.
about deleting list, i'm not sure your way, but i assumed to delete only
when match.
in both cases, presuming the list is in colunm A

Sub insertlist()
Dim strin, s
Dim stcell As Range, lsrange As Range
Const cl = "a" '<<== column data in and Change here

On Error Resume Next
strin = Application.InputBox("input data", 1 + 2)
If VarType(strin) = vbBoolean Then
Exit Sub
End If

Set stcell = Cells(1, cl)
Set lscell = Cells(Cells(Cells.Rows.count, cl).End(xlUp).Row, cl)
s = Application.Match(strin, Range(stcell, lscell))
If Not IsError(s) Then
stcell(s + 1).EntireRow.Insert
stcell(s + 1) = strin
Else
stcell.EntireRow.Insert
stcell(0) = strin
End If
End Sub

Sub deletelist()
Dim strin, s
Dim stcell As Range, lsrange As Range
Const cl = "a" '<<== column data in and Change here

On Error Resume Next
strin = Application.InputBox("input data", 1 + 2)
If VarType(strin) = vbBoolean Then
Exit Sub
End If

Set stcell = Cells(1, cl)
Set lscell = Cells(Cells(Cells.Rows.count, cl).End(xlUp).Row, cl)
s = Application.Match(strin, Range(stcell, lscell), 0)
If Not IsError(s) Then
stcell(s).EntireRow.Delete
Else
MsgBox "data[ " & strin & " ] not found"
End If
End Sub

keizi

"Tyrell" <Tyrell@discussions.microsoft.com> wrote in message
news:6138BF5A-B430-4896-B312-D4E47493020C@microsoft.com...
> I have a list that is organized alphabetically in a column. I want
the user
> to enter an item to add to the list in a text box, then click a button
to
> have the text box information added to my list. How do I INSERT the
> information into the list in alphabetical order and keep all original
list
> data? Then, how would I go about deleting an item in the same manner?
> --
> Tyrell Fickenscher
> Plant Manager / Agronomist