none
Delete rows and shift up based on certain criteria RRS feed

  • Question

  • Hello,

    I'm working on this user form and using the excel sheets like a DB which I know isn't the best way but it isn't in my control.

    The user form allows a user to check which days they worked on certain projects.  When they save I put the date, user and project selected in the data tab.

    When they open the project it will load the current week and show them if they already added any data to the app by showing checked checkboxes for the dates and projects they added before.

    My issue is that if they uncheck a checkboxes that they checked before I need to remove that row from my data sheet. 

    What I was thinking is if they click save I would remove every row for that week and re-add anything that is checked.  To do this I need to filter and then delete the entire row.  What is the best way to do this?

    Thank you

    Tuesday, May 5, 2015 1:40 PM

Answers

  • Hi,

    >> My issue is that if they uncheck a checkboxes that they checked before I need to remove that row from my data sheet. 

    Based on my understanding, you want to delete some rows when the checkbox is unchecked before the workbook save.  You could use add the delete rows action in Workbook_BeforeSave Event to delete the rows. The following code you could refer to as a sample, which will delete all the rows while value for A1 equals to today’s date.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If UserForm1.CheckBox1.Value = False Then
    Call DeleteRows
    End If
    End Sub
    
    Sub DeleteRows()
    row_number = 1
    Do
    DoEvents
    row_number = row_number + 1
    DateData = ActiveWorkbook.Sheets(4).Range("A" & row_number)
    If InStr(DateData,  Date)>= 1 Then
    ActiveWorkbook.Sheets(4).Rows(row_number & ":" & row_number).Delete
    row_number = row_number - 1
    End If
    Loop Until deleData = ""
    End Sub
    

    Hope this could help you

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Tuesday, May 19, 2015 12:02 AM
    Tuesday, May 12, 2015 9:29 AM
    Moderator

All replies

  • Really need to see an example workbook. Can you remove any sensitive data and replace with dummy data and then upload a copy to OneDrive.

    Guidelines to upload a workbook on OneDrive:

    1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Do NOT fill in the form; Select "Get a Link" on the left side.
    10. Click the button "Create a Link"
    11. Click in the box where the link is created and it will highlight.
    12. Copy the link and paste into your reply on this forum.

    Regards, OssieMac

    Wednesday, May 6, 2015 1:08 AM
  • Hi,

    >> My issue is that if they uncheck a checkboxes that they checked before I need to remove that row from my data sheet. 

    Based on my understanding, you want to delete some rows when the checkbox is unchecked before the workbook save.  You could use add the delete rows action in Workbook_BeforeSave Event to delete the rows. The following code you could refer to as a sample, which will delete all the rows while value for A1 equals to today’s date.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If UserForm1.CheckBox1.Value = False Then
    Call DeleteRows
    End If
    End Sub
    
    Sub DeleteRows()
    row_number = 1
    Do
    DoEvents
    row_number = row_number + 1
    DateData = ActiveWorkbook.Sheets(4).Range("A" & row_number)
    If InStr(DateData,  Date)>= 1 Then
    ActiveWorkbook.Sheets(4).Rows(row_number & ":" & row_number).Delete
    row_number = row_number - 1
    End If
    Loop Until deleData = ""
    End Sub
    

    Hope this could help you

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Tuesday, May 19, 2015 12:02 AM
    Tuesday, May 12, 2015 9:29 AM
    Moderator