Print routine not working with locked cells

[Ian Bertie]

Good morning Guru's

I have created a print routine as below to hide unused rows, however if I try to lock certain cells to prevent change, the routine fails with the error message "Run time error 1004 Unable to set the hidden property of the range class"

Sub Print_NonBlank_Rows()
Dim rng As Range, cell As Range, toHide As Range
Dim r&, c%, b%, n%
Set rng = ActiveSheet.UsedRange
For r = 1 To rng.Rows.Count
b = 0
For c = 1 To rng.Columns.Count
If rng.Cells(r, c).Value = "" Then
b = b + 1
End If
Next c
If b = rng.Columns.Count Then
If n = 0 Then
Set toHide = rng.Cells(r, c).EntireRow
n = 1
Else
Set toHide = Union(toHide, rng.Cells(r, c).EntireRow)
End If
End If
Next r
toHide.EntireRow.Hidden = True
ActiveSheet.PrintOut Copies:=1
toHide.EntireRow.Hidden = False
End Sub


Any ideas please

 

[jmacleary]

Hello there. You will have to temporarily unlock the worksheet, run your code, then lock it again. Since most of your code will work on the protected sheet, I have only unprotected it for the minimum number of steps. Try this (untested):

Sub Print_NonBlank_Rows()
    Dim rng As Range, cell As Range, toHide As Range
    Dim r&, c%, b%, n%
    Set rng = ActiveSheet.UsedRange
    For r = 1 To rng.Rows.Count
        b = 0
        For c = 1 To rng.Columns.Count
            If rng.Cells(r, c).Value = "" Then
                b = b + 1
            End If
        Next c
        If b = rng.Columns.Count Then
            If n = 0 Then
                Set toHide = rng.Cells(r, c).EntireRow
                n = 1
            Else
                Set toHide = Union(toHide, rng.Cells(r, c).EntireRow)
            End If
        End If
    Next r
    ActiveSheet.Unprotect
  
    toHide.EntireRow.Hidden = True
    ActiveSheet.PrintOut Copies:=1

    toHide.EntireRow.Hidden = False
    ActiveSheet.Protect
End Sub

 

[Ian Bertie]

Hello there. You will have to temporarily unlock the worksheet, run your code, then lock it again. Since most of your code will work on the protected sheet, I have only unprotected it for the minimum number of steps. Try this (untested):

Sub Print_NonBlank_Rows()
    Dim rng As Range, cell As Range, toHide As Range
    Dim r&, c%, b%, n%
    Set rng = ActiveSheet.UsedRange
    For r = 1 To rng.Rows.Count
        b = 0
        For c = 1 To rng.Columns.Count
            If rng.Cells(r, c).Value = "" Then
                b = b + 1
            End If
        Next c
        If b = rng.Columns.Count Then
            If n = 0 Then
                Set toHide = rng.Cells(r, c).EntireRow
                n = 1
            Else
                Set toHide = Union(toHide, rng.Cells(r, c).EntireRow)
            End If
        End If
    Next r
    ActiveSheet.Unprotect
 
    toHide.EntireRow.Hidden = True
    ActiveSheet.PrintOut Copies:=1

    toHide.EntireRow.Hidden = False
    ActiveSheet.Protect
End Sub

Thank you John
This works, the only change I made was to add the password so the user does not need to enter
Regards
Ian