VBA code working on one file, giving error on another

[wenifede]

Hi, I've two Excel with Macro files. I copied a couple of pages from one to another. I had two macros, one for exporting to Pdf which is working fine also in the new file, and another one that would hide/show a section of a document.<br>This second one keeps giving error, while in the older file it's still working fine.<br>I don't understand why it's giving error. Can somebody help?

Sub ShowHideHP2()
' RemovePbreak Macro
Sheets("QUOTE").Select
Rows("318:536").Select
    If Selection.EntireRow.Hidden = True Then
   Selection.EntireRow.Hidden = False
   'Add Row Page Break
   Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakManual
   Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakManual
   Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakManual
   Else
   Selection.EntireRow.Hidden = True  (ON THIS LINE IS GIVING ERROR)
   'Clear Row Page
   Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakNone
   Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakNone
   Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakNone
End If
Sheets("Main calcs").Select
End Sub

[June7]

In future, please post code between CODE tags to retain structure and readability.

If you want to provide file for analysis, follow instructions at bottom of my post.

[Aussiebear]

Does this work for you?

Sub ShowHideHP2()
' RemovePbreak Macro
With Sheets("QUOTE”).Rows("318:536")
 If Selection.EntireRow.Hidden = True Then
     'Clear Row Page
      Rows(318,405,471).PageBreak = xlPageBreakNone
     Elseif
     Selection.EntireRow.Hidden = False
     'Add Row Page Break
      Rows(318,405,471).PageBreak = xlPageBreakManual
End If
Sheets("Main calcs").Activate
End Sub

[wenifede]

Hi Aussiebear,
thanks for this. It doesn't work. It gives me syntax Error in lines
With Sheets("QUOTE”).Rows("318:536")
and Elseif

However, I found what was wrong in my code... nothing... I locked the Excel page and that prevented the Macro to work property.
However, thanks for this, because you made me realise what the error was.

Thanks

[Aussiebear]

Thank you for that. I am struggling with the logic of your argument however. If Rows (318:536) are visible insert page breaks but if Rows (318:536)are not visible remove page breaks..... my question is if the rows are not visible why do anything with them?

[Grade4.2]

Greaty point Aussiebear. I wonder if he has any merged cells in range 318 to 536?
If so, the following might help:

Sub ShowHideHP2()    ' RemovePbreak Macro
    Dim cell As Range
    
    Sheets("QUOTE").Select
    Rows("318:536").Select
    
    ' Unmerge any merged cells in the selected range
    For Each cell In Selection
        If cell.MergeCells Then
            cell.MergeArea.UnMerge
        End If
    Next cell
    
    If Selection.EntireRow.Hidden = True Then
        Selection.EntireRow.Hidden = False
        'Add Row Page Break
        Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakManual
        Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakManual
        Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakManual
    Else
        Selection.EntireRow.Hidden = True
        'Clear Row Page
        Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakNone
        Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakNone
        Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakNone
    End If
    Sheets("Main calcs").Select
End Sub