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