Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    26

    Unanswered: Excel checkbox to delete row

    I have a simple Excel sheet, that just lists lots of out standing jobs that I have at the moment at work, I would like to stick a check box on the last column and once clicked it will delete the entire row.

    Can any one help or do you need more information

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    It can be done. However, a question for you. Would it make more sense to check the cell, then have that row hidden rather than deleted? This would allow you to have a record of completed projects as well as current ones.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2003
    Posts
    26
    That sounds interesting, it would be good to click the cell, button, check box and delete the row from that sheet and then paste it in on to another sheet at the next availble row. Then this way I would have a record of which jobs I have done.

    Im thinking Access would be a lotter easier, but I would prefer to do this in Excel can you offer any advice?

  4. #4
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203

    Re: Excel checkbox to delete row

    Another way to handle this is by using a filter. In column A, you'd place a "C" (for complete), then via a sheet activation marco all items marked as complete would hide and all open items would be shown. I also like the idea of maintaning a historical record.

    Originally posted by teaboy
    I have a simple Excel sheet, that just lists lots of out standing jobs that I have at the moment at work, I would like to stick a check box on the last column and once clicked it will delete the entire row.

    Can any one help or do you need more information
    Rick Knight
    KnightShift Office Solutions and Horse Breaking
    VB, VBA, FileMaker, Access Solutions, Web Solutions

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, here is one approach.

    Be sure that when you start that you do not have a worksheet with the name "Backup" in the workbook. This macro assumes your primary worksheet is called "Work" (where you add your new projects and keep track of when they are completed). If a worksheet is already named "Archive" then the macro will rename it "Backup." Then it will make a copy of the "Work" worksheet, and rename it "Archive."

    If "X" in column H indicates a completed project, then the macro will check on "Archive" to delete any row that has no "X" in the column you select (i.e. all non-complete projects will be deleted from Archive.

    NOTE: This means that "Work" will continue to carry all projects (completed or not completed)

    Code:
    Sub DeleteEmptyRowsMain()
    ' This portion will make a new Archive
    Dim myColm As Range
        Sheets("Archive").Name = "Backup"
        Sheets("Work").Activate
        ActiveSheet.Copy Before:=Sheets(1)
        ActiveSheet.Name = "Archive"
        
        Set myColm = Range("H:H")
        On Error Resume Next
        myColm.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        Sheets("Backup").Delete
        Sheets("Work").Select
    
    Dim c As Range
        'Loop through cells H2:H65536 and delete cells that contain an "x."
        For Each c In Range("H1:H65536")
            If c = "x" Then c.EntireRow.Delete
        Next
    End Sub
    There is a little tweaking to be done, but I will have to wait a little while to provide it for you. You can test this on a COPY of your workbook.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203

    Re: Excel checkbox to delete row

    The simplest solution was to simply get the number from the range of cells via a sum function. I saw from your sheet that the row's contained only one number and if that's the case..well see attached.

    Originally posted by teaboy
    I have a simple Excel sheet, that just lists lots of out standing jobs that I have at the moment at work, I would like to stick a check box on the last column and once clicked it will delete the entire row.

    Can any one help or do you need more information
    Attached Files Attached Files
    Rick Knight
    KnightShift Office Solutions and Horse Breaking
    VB, VBA, FileMaker, Access Solutions, Web Solutions

  7. #7
    Join Date
    Dec 2003
    Posts
    26
    This is looking good I will have a play with the code, I have included an xls file to show you roughly what i mean to do.

    Originally posted by shades
    Okay, here is one approach.

    Be sure that when you start that you do not have a worksheet with the name "Backup" in the workbook. This macro assumes your primary worksheet is called "Work" (where you add your new projects and keep track of when they are completed). If a worksheet is already named "Archive" then the macro will rename it "Backup." Then it will make a copy of the "Work" worksheet, and rename it "Archive."

    If "X" in column H indicates a completed project, then the macro will check on "Archive" to delete any row that has no "X" in the column you select (i.e. all non-complete projects will be deleted from Archive.

    NOTE: This means that "Work" will continue to carry all projects (completed or not completed)

    Code:
    Sub DeleteEmptyRowsMain()
    ' This portion will make a new Archive
    Dim myColm As Range
        Sheets("Archive").Name = "Backup"
        Sheets("Work").Activate
        ActiveSheet.Copy Before:=Sheets(1)
        ActiveSheet.Name = "Archive"
        
        Set myColm = Range("H:H")
        On Error Resume Next
        myColm.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        Sheets("Backup").Delete
        Sheets("Work").Select
    
    Dim c As Range
        'Loop through cells H2:H65536 and delete cells that contain an "x."
        For Each c In Range("H1:H65536")
            If c = "x" Then c.EntireRow.Delete
        Next
    End Sub
    There is a little tweaking to be done, but I will have to wait a little while to provide it for you. You can test this on a COPY of your workbook.
    Attached Files Attached Files

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, I used your example sheet and made some changes. You can do all this without any VBA, but it will require a couple of steps. Also, rather than an "X" in the Completed column (Column D), I used dates - that may prove handy for future reference.

    1. I assigned dynamic named ranges for the worksheet "OutstandingJobs"

    For column A, this is named Job_Number
    (remember, you can't have any spaces in named ranges), then this formula is in the "Refers to" box.

    =OFFSET(OutstandingJobs!$A$1,0,0,COUNTA(Outstandin gJobs!$A:$A),1)

    I did the same for the other columns, and for the entire table I named it

    WorkLog and used this formula:

    =OFFSET(OutstandingJobs!$A$1,0,0,COUNTA(Outstandin gJobs!$A:$A),COUNTA(OutstandingJobs!$1:$1))

    -----------------------------

    Now, on Completed Jobs, I used lookup tables with the name ranges. But I also put that within an IF statement. Now you can copy down each column as far as you want. If the corresponding column on OutstandingJobs is blank, then it will be blank in this table. But the minute you enter in the value in OutstandingJobs, it will automatically appear in CompletedJobs.

    Now for procedure, if a job is complete, fill in the date, and it will automatically fill in the CompletedJobs table. Then on that table (CompletedJobs), select only those rows that have a "Date Completed" entry (in Column D), and copy, Paste Special, and choose "Values" and click OK.

    Then go back to OutstandingJobs, and delete all jobs in which the "Date Completed" column (Column D) has been filled in.

    Now, you OutstandingJobs will have only current work, and your CompletedJobs will have the archive and will also keep up with the current work.

    HTH
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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