Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Updating/deleting rows in a spreadsheet table

  1. #1
    Guest

    Default

    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

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Guest

    Default

    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


  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •