Allow Macro to Run on Locked Sheet

Hey everyone!

I have a recorded macro to filter out all blank cells in my table. The macro looks like this:

Sub filter_blank()

' filter_blank Macro

' Keyboard Shortcut: Ctrl+j

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria:=_

"<>"

End Sub

The problem I'm having, is that the macro can't run when the sheet is protected - this sheet needs to stay protected as other users will be using this.

I've tried searching ways to use a VBA code or edit the Macro to unprotect the sheet, run the macro, and protect the sheet again. I've tried a few different codes and edits to the Macros...however nothing has worked yet. Then again, I'm no coder, so it's possible I had a period or comma or something out of place. If you know a way to allow the macro to run on the protected sheet please let me know!

For reference, the sheet is Sheet2, and the data is in Table1

I appreciate any ideas!

Cami96 -

Did you try https://www.thespreadsheetguru.com/the-code-vault/insert-delete-table-rows-with-worksheet-protection?

What you accomplished with the Macro Recorder you can also create as:

Option Explicit
Dim tableToDeleteRows As ListObject

Sub deleteEmptyTableRows()

    Set tableToDeleteRows = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
    
    Dim i As Long
    
    With tableToDeleteRows
        For i = .ListRows.Count To 1 Step -1
            If Trim(.ListRows(i).Range.Cells(1)) = vbNullString Then
                .ListRows(i).Delete
            End If
        Next i
    End With

End Sub

It's a nice little procedure to get you into thinking programmatically.

4 Replies

Abiola1
MVP
Jan 18, 2020
Kindly see the link below

https://excel.tv/how-to-run-macros-in-protected-worksheets-excel-vba-tips/
- Cami96
  Copper Contributor
  Jan 21, 2020
  Thank you Abiola1
ChrisMendoza
Iron Contributor
Jan 17, 2020
Cami96 -

Did you try https://www.thespreadsheetguru.com/the-code-vault/insert-delete-table-rows-with-worksheet-protection?

What you accomplished with the Macro Recorder you can also create as:

Option Explicit Dim tableToDeleteRows As ListObject Sub deleteEmptyTableRows() Set tableToDeleteRows = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1") Dim i As Long With tableToDeleteRows For i = .ListRows.Count To 1 Step -1 If Trim(.ListRows(i).Range.Cells(1)) = vbNullString Then .ListRows(i).Delete End If Next i End With End Sub

It's a nice little procedure to get you into thinking programmatically.
- Cami96
  Copper Contributor
  Jan 21, 2020
  Thank you so much ChrisMendoza ! I appreciate how you reconstructed my recorded macro. It really helps to see it as a code!

Forum Discussion

Allow Macro to Run on Locked Sheet

4 Replies

Resources