Remove data from column

[Steve]

Hi all. I have the following bit of code that presents an input box
for a user to add data to the next available cell in column A. I
would like to now present the user with a pop-up menu picklist that
will display all entries in column A, allow them to pick on, and upon
clicking OK remove that selected item from column A.

Any ideas? Thanks!

Sub NewCategory()

Dim ans As String

ans = InputBox("Enter New Category", "New Category")

Sheets("Cat Picklist").Cells(2, "a").End(xlDown).Offset(1) = ans

End Sub

 

[Don Guillett]

If you only want to delete 1, just do it. I assume??? you want to
delete more. ??Explain. Perhaps using data>filter>autofilter is the
simple answer.

 

[Steve]

Hi Don. The column of data, and field that I want to delete is in a
hidden sheet. The column of data feeds a dynamic named range, which
is used in a data validation picklist on a different sheet. So yes, I
would usually only delete one field...just in a nice user interface
vs. toggling over to another sheet, which we have hidden. This file
will be used in a conference room setting, projected on the wall...so
we're trying to make changes quickly and seem somewhat
sophisticated

 

[Don Guillett]

Hi Don.  The column of data, and field that I want to delete is in a
hidden sheet.  The column of data feeds a dynamic named range, which
is used in a data validation picklist on a different sheet.  So yes, I
would usually only delete one field...just in a nice user interface
vs. toggling over to another sheet, which we have hidden.  This file
will be used in a conference room setting, projected on the wall...so
we're trying to make changes quickly and seem somewhat
sophisticated

If desired, send your file to dguillett1 @gmail.com with this msg and
a complete explanation.

 

[merjet]

Insert a UserForm to your VBA Project. Put a ComboBox and a
CommandButton on the UserForm.
Add the following code to the UserForm:

Private mRng As Range
Private Sub CommandButton1_Click()
mRng(ComboBox1.ListIndex + 1).Delete
ComboBox1.ListIndex = -1
ComboBox1.Clear
UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
Set mRng = Sheets("Cat Picklist").Range(Cells(2, "a"), Cells(2,
"a").End(xlDown))
For Each c In mRng
ComboBox1.AddItem c
Next c
End Sub

Add the following to the module with Sub NewCategory:

Sub DeleteCategory()
UserForm1.Show
End Sub

Hope that helps.