+ Reply to Thread
Results 1 to 4 of 4

Advanced record delete.

  1. #1
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Advanced record delete.

    I am currently using the following macro to delete rows:
    Please Login or Register  to view this content.
    How can I take it further so that if the user does not have an active cell selection between (and including) Columns A to K, from row 6 to the last record then is displays the message "You must select a record to be deleted'.
    Also if the user selects multiple rows then the message box would instead display the message 'Are you sure you want to delete the records (display the A cell of the first row in the selection) to (display the A cell of the last row in the selection)?
    Then erase every row in the selection.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Advanced record delete.

    you can try the following macro:
    Please Login or Register  to view this content.
    Last edited by p24leclerc; 01-16-2014 at 08:24 PM.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Advanced record delete.

    Thank you Pierre, however I would like to make a couple of changes if possible. When there is only one cell selected (and therefore only one row/record bing deleted) it currently displays the record number twice (as its expecting multiple rows). In this situation only can it instead display a different message where the record number is only listed once. So it would read: 'Are you sure you want to delete record number (then display the record number from the A column).'
    Also its having difficulty in correctly finding the last record, which I think is because I have formulas in a couple of the columns. I am currently using the following code to successfully find the last row using the G column since the G column does not contain formulas. Can a similar code be used with this code to use G as the column to find the last row?

    Cells(Rows.Count, "G").End(xlUp).Offset(1).Select

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Advanced record delete.

    Here is a modification to show only one record to delete in case only one row is selected.
    Please Login or Register  to view this content.
    In the code, I use column K to find the last row of the table because it is also the last column and that we want to be sure any selected cells of the table will trigger the deletion. You can replace the SET line of code with this one if you like. it defines a range from cell A6 to cell K?. The row number is found by looking at the last non-empty cell in a column (actually it is column 1) you select yourself a column which you are sure there will never be something below your table. This is not affected by the cells' content. It can be either a fixed value or a formula.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-16-2012, 09:25 AM
  2. Delete a record from a database
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-01-2010, 06:21 PM
  3. Advanced filter- Specify unwanted record to show
    By leecs in forum Excel General
    Replies: 7
    Last Post: 10-08-2007, 06:40 AM
  4. [SOLVED] delete record based on a value
    By Anauna in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-27-2005, 04:05 PM
  5. delete the record
    By Jimmy in forum Excel General
    Replies: 2
    Last Post: 06-23-2005, 04:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1