Delete data in selected cells of a filtered range

[sparky2205]

HI folks,
I have a protected spreadsheet which has every second row editable.
At the end of the year I want to be able to delete all of the information from the editable rows of the sheet.
When I filter on the editable rows, select all and press delete, I get the error telling me the sheet is protected.
I am selecting by highlighting the first cell then control and click to the last cell.
I assume this is because when I select the filtered cells the delete is looking through the filter and also attempting to delete the information in the protected cells.

Is there any way to achieve this manually? It is too risky to provide a button linked to VBA. Someone will accidentally delete information half way through the year.

Thanks.

Additional info:
I do not have the option of manually unprotecting the sheet to perform the deletion.

 

[Peter_SSs]

Not sure it would be easy to set up a replica sheet but would this work for you?

When I filter on the editable rows, select all ...

Then
F5 -> Special... -> Visible cells only -> OK
Then

.. and press delete,

 

[sparky2205]

Hi Peter,
thanks for the response.
If I'm reading your message correctly I should:

Apply my filter
F5 -> Special to select visible cells only
Delete the selected cells.


Two problems:
I am only deleting the contents of certain cells within the filtered rows. And there are locked cells with data I don't want to delete in other columns of the same rows. My apologies, I should have made that clear.
But regardless, the Special option in Go To doesn't appear to work on a protected sheet. At least it's greyed out on my sheet.

 

[Peter_SSs]

Is there any way to achieve this manually? It is too risky to provide a button linked to VBA. Someone will accidentally delete information half way through the year.

If a user might use vba to delete the data at the wrong time, surely they could also use the manual method to delete the data at the wrong time?

Could you consider a macro that had a safety mechanism? For example ..
- The macro might first ask for a password (which you know but the other users don't) before doing the deletions.
- The macro might prevent deletions unless the current date is between 5th August and 12th August.
etc

 

[sparky2205]

Good morning Peter,
you are right of course, a user could delete the data manually. But to do this they would have to filter the data and delete it row by row. This would have to be a very deliberate action.
What I'm worried about is someone being able to press a button accidentally and delete all the data.
Of course I could have a warning message telling the user they are about to delete all the data but this is an important file and I don't want to be responsible for giving them that option.
The data deletion activity will only be carried out once a year when the file for the next year is being prepared so I'm just going to have them complete that task manually. This can be achieved without having to unprotect the spreadsheet which is my ultimate aim as the spreadsheet will be validated.
I do like your idea of the protected macro though. I'll play around with that when I get some time, just to see if I can get it to work.

Thanks for your help Peter.
Have a good day, or night, as it is with you.

 

[Peter_SSs]

I do like your idea of the protected macro though. I'll play around with that when I get some time, just to see if I can get it to work.

I don't know when your year ends or just when you would want to allow the deletions but this is what I had in mind. It would only allow deletions in the period 5th to 12th August, and only if the user confirmed the action.

Sub Clear_Data()
  Dim Resp As VbMsgBoxResult
  
  If Month(Date) = 8 And Day(Date) >= 5 And Day(Date) <= 12 Then
    Resp = MsgBox("Are you sure you want to delete data?", vbYesNoCancel)
    If Resp = vbYes Then
      'Code here to do the deletion
    Else
      MsgBox "OK, no deletions"
    End If
  Else
    MsgBox "Unable to delete at this time"
  End If
End Sub

... but this is an important file ..

So there would be backups if something did happen to go wrong?

 

[sparky2205]

Yes, there would be a backup. The file is a network file so the usual daily backup would apply. At a max 24 hours of data would be lost.
But this is not a file that changes every hour, nor even every day. And changes to the file are driven by controlled documents. It would be easy to restore any lost data.