Updating/deleting rows in a spreadsheet table

I would like to be able to delete a row of a table without going straight to the worksheet to change it. Instead I would like to be able to type in a name on another worksheet, which will look up the appropriate row in the table and delete it.

I would like to be able to edit the details in a table in a similar fashion.

Has anyone got any ideas?

Thanks

Richard

[Damon Ostrander]

Hi Richard,

This sounds fairly straightforward to do, but a little clarification would be helpful.

1. When you mention looking up a value in the table, do you mean anywhere in the table? Or do you mean just in the first column of the table?

2. Is the table in a named range?

3. Do you want to enter the lookup value in a cell, and the instant you do so have the row in the table irrevocably deleted without veto opportunity? (a bit dangerous)

4. Does the match have to be exact, or should it be case-insensitive, and should it ignore leading/trailing blanks? Or is it perhaps numeric and just needs to be within some tolerance?

Thanks for your reply.

1. When you mention looking up a value in the table, do you mean anywhere in the table? Or do you mean just in the first column of the table?

The first column would do fine.

2. Is the table in a named range?

Yes, the table is named and the cells are named with the column headings are named as well.

3. Do you want to enter the lookup value in a cell, and the instant you do so have the row in the table irrevocably deleted without veto opportunity? (a bit dangerous)

It would be better for there to be a warning box.

4. Does the match have to be exact, or should it be case-insensitive, and should it ignore leading/trailing blanks? Or is it perhaps numeric and just needs to be within some tolerance?

Case sensitive is not important. It would be better if text could be typed in - a user's name - but I could add a numerical user ID field and look up from there if necessary. It really would be better if the user could select a name from a combo box, say, to choose which record to delete.

Cheers

Richard

[Damon Ostrander]

Hi Richard,

Sorry it has taken me so long to get back to your problem. Things have been rather hectic here. Anyway, here is an example of some code that I believe does what you describe. The example is for a table range named MyTable on a worksheet named Table Data.

Sub DelTableRow()
Dim DelName As String
Dim DelCell As Range
Dim Table As Range

TryAgain:
DelName = InputBox("Enter name to be deleted from table", _
"Delete Table Row")

If DelName = "" Then Exit Sub 'user cancelled

Set Table = Worksheets("Table Data").Range("MyTable")
Set DelCell = Table.Columns(1).Find(DelName)
If DelCell Is Nothing Then GoTo TryAgain
'delete entire row of table
Application.Intersect(DelCell.EntireRow, Table).Delete xlShiftUp
Beep

End Sub