Updating/deleting rows in a spreadsheet table

G

Guest

Guest
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top