hide xrange and protect cells

[ho_gh_m]

Hi
I have a formula :

Dim xRg As Range
        For Each xRg In Range("A2:A21")
            If xRg.Value = "" Then
                If xRg.EntireRow.Hidden = False Then
                    xRg.EntireRow.Hidden = True
                End If
            Else
                If xRg.EntireRow.Hidden = True Then
                    xRg.EntireRow.Hidden = False
                End If
            End If
        Next xRg
    Application.ScreenUpdating = True

With this formula, the rows of a range where nothing is written are hidden ("")
But
When I protect the sheet, it gives error :

Run-time error '1004':
unable to set the Hidden property of the Range class

please help me
thanks

 

[ho_gh_m]

Hi
I have a formula :

Dim xRg As Range
        For Each xRg In Range("A2:A21")
            If xRg.Value = "" Then
                If xRg.EntireRow.Hidden = False Then
                    xRg.EntireRow.Hidden = True
                End If
            Else
                If xRg.EntireRow.Hidden = True Then
                    xRg.EntireRow.Hidden = False
                End If
            End If
        Next xRg
    Application.ScreenUpdating = True

With this formula, the rows of a range where nothing is written are hidden ("")
But
When I protect the sheet, it gives error :

Run-time error '1004':
unable to set the Hidden property of the Range class

please help me
thanks

Private Sub Worksheet_Activate()
Dim xRg As Range
'    Application.ScreenUpdating = False
        For Each xRg In Range("A2:A21")
            If xRg.Value = "" Then
                If xRg.EntireRow.Hidden = False Then
                    xRg.EntireRow.Hidden = True
                End If
            Else
                If xRg.EntireRow.Hidden = True Then
                    xRg.EntireRow.Hidden = False
                End If
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple of options would be:

1. When you protect the sheet, set the option to allow rows to be formatted.
   
   
   
   
   
   
2. Put a line at the start of your code to unprotect the worksheet and another line at the end of your code to re-protect it.

BTW, you can shorten that hide/unhide code a great deal. For example ..

Private Sub Worksheet_Activate()
  Dim xRg As Range
  
  Application.ScreenUpdating = False
  For Each xRg In Range("A2:A21")
    xRg.EntireRow.Hidden = xRg.Value = ""
  Next xRg
  Application.ScreenUpdating = True
End Sub