Good day. Help with the macro. We need a macro that will clear all unprotected filled cells on the sheet. The sheet is protected with a password. I've found this, but swears at "uss.Select". Tell me please.

Sub Clear() Dim ss As Range, uss As Range For Each ss In Selection If Not ss.Locked Then If uss Is Nothing Then Set uss = ss Else Set uss = Union(uss, ss) End If End If Next uss.Select End Sub

    1 answer 1

     uss.Select

    This line selects unprotected cells of the previously selected range ( In Selection - in the selected range). But you need to delete the data:

      uss.ClearContents

    If there are no unprotected cells in the selected range, your code will fall out into error. It is necessary to check the filling range:

     If Not uss Is Nothing Then uss.ClearContents

    The macro works with the selected range. If you need to clear all unprotected cells in the sheet, you can work with the UsedRange - custom range.

    The code can be abbreviated if at least one cell is known in advance that needs to be cleared. Such a cell can be any empty or one, the deletion of data in which does not harm further work. Naturally, it must be unprotected.

     Sub Clear_Cell() Dim r As Range, ur As Range Set ur = Cells(1, 1) ' ΠΎΡ‡ΠΈΡ‰Π°Π΅ΠΌΡƒΡŽ ячСйку - Π² Ρ„ΠΎΡ€ΠΌΠΈΡ€ΡƒΠ΅ΠΌΡ‹ΠΉ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ ' For Each r In ActiveSheet.UsedRange ' провСряСм ячСйки ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»ΡŒΡΠΊΠΎΠ³ΠΎ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π° ' If Not r.Locked Then Set ur = Union(ur, r) ' Π½Π΅Π·Π°Ρ‰ΠΈΡ‰Π΅Π½Π½Ρ‹Π΅ - Π² Ρ„ΠΎΡ€ΠΌΠΈΡ€ΡƒΠ΅ΠΌΠΌΡ‹ΠΉ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ ' Next r ur.ClearContents ' ΠΎΡ‡ΠΈΡ‰Π°ΠΌ сформированный Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ ' Set ur = Nothing ' освобоТдаСм ΠΏΠ°ΠΌΡΡ‚ΡŒ ' End Sub

    Along the way: do not call procedures, variables, and so on. reserved words. It is better to call the macro differently, for example: Sub Clear_Cell () .

    If an object was assigned to a variable, it is desirable to clear the variable at the end of the work ( Set ur = Nothing )

    • why not just do it For Each c in ActiveSheet.UsedRange.Cells If Not c.Locked Then c.ClearContents Next c .
    • Can. But in this case, with each cleaning of the cell, we turn to the sheet, and working with sheet objects is slow (naturally, the difference is insignificant in a small range). When forming a range, cleaning occurs at once. - vikttur
    • vikttur, thank you very much. First, I realized that my macro works if I select the range myself. But Vah option is much more convenient. But in your code the error, swears on the line "ur.ClearContents". Writes that this cell is on a protected sheet. Very grateful, Eduard Izmalkov. Having removed the "ur.ClearContents" code and replacing the loop with yours, everything now works just perfectly. Thank you very much. - Vitaliy Fesyura
    • Set ur = Cells (1, 1) - if the A1 cell is protected, there will be an error. If an unprotected cell is placed in the created range, there should be no errors β€” the rest of the cells written to the range are checked in a loop. - vikttur