Removing borders when printing using vba

[sparky2205]

Hi folks,
I have a spreadsheet which looks like this:

It is adapted from another worksheet via conditional formatting to only show populated cells.
The original worksheet looks like this:

I use the following vba to collapse unused rows and print out a compact report, as above.
VBA:

Sub PrintoutData()

Dim pw As String
pw = ""

On Error GoTo errhandler

    'Unprotect the workbook
    ActiveWorkbook.Unprotect Password:=pw
    'Prevent screen flashing
    Application.ScreenUpdating = False
    'Make the worksheet "Print Data" visible
    ActiveWorkbook.Worksheets("Print Data").Visible = True

'Resize (i.e. hide) nonactive rows
    ' Each If statement checks if the Qty Inspected is "n/a".  If it is, hide those three rows.
    If Worksheets("Print Data").Range("C8").Value = "n/a" Then
        Worksheets("Print Data").Range("A8:A10").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C11").Value = "n/a" Then
        Worksheets("Print Data").Range("A11:A13").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C14").Value = "n/a" Then
        Worksheets("Print Data").Range("A14:A16").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C17").Value = "n/a" Then
        Worksheets("Print Data").Range("A17:A19").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C20").Value = "n/a" Then
        Worksheets("Print Data").Range("A20:A22").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C23").Value = "n/a" Then
        Worksheets("Print Data").Range("A23:A25").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C26").Value = "n/a" Then
        Worksheets("Print Data").Range("A26:A28").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C29").Value = "n/a" Then
        Worksheets("Print Data").Range("A29:A31").EntireRow.Hidden = True
    End If
    
    If Worksheets("Print Data").Range("C32").Value = "n/a" Then
        Worksheets("Print Data").Range("A32:A34").EntireRow.Hidden = True
    End If

    'Preview the printed worksheet
    ActiveWorkbook.Worksheets("Print Data").PrintPreview
    'Resize all rows
    Worksheets("Print Data").Range("A8:A34").EntireRow.Hidden = False
    'Hide the worksheet "Print Data"
    ActiveWorkbook.Worksheets("Print Data").Visible = False
    
    'Reportect the workbook
    ActiveWorkbook.Protect Password:=pw
    'Turn Screen Updating back on
    Application.ScreenUpdating = True

Exit Sub

errhandler:
    Application.ScreenUpdating = True
    ActiveWorkbook.Worksheets("Print Data").Visible = False
    ActiveWorkbook.Protect Password:=pw

End Sub

The problem:
Even though the first screenshot suggests all is ok, when I print I get this:

Highlighted in blue, I am getting these extra lines. They are appearing exactly where rows have been collapsed.

I have tried adding variations of things like:

Worksheets("Print Data").Range("A34:AA34").Borders.LineStyle = xlNone

into the vba but to no effect.

Does anyone have any ideas?

 

[sparky2205]

Just giving this a bump in case someone who might be able to help missed it.
I still haven't managed to come up with a solution.

 

[Alex Blakenburg]

I can't see your row numbers but given that you are hiding rows 32:34 and trying to remove the border from row 34, it is likely that what you are seeing is either the bottom border of row 31 or the top border of 35.

If you can be a bit more specific or perhaps share a copy of the workbook minus the data but with the rows hidden per your image above I can take a closer look.
Be aware that setting a top or bottom edge border to xlNone may not actually clear a border you can see since there is also an xlinsidehorizontal that comes into play.
Your .Borders.LineStyle = xlNone will clear them all though.

 

[sparky2205]

Hi Alex,
thanks for taking a look at this for me. This is a link to the file: Test visual in-process test - 25 Aug 22.xlsm
The password is Test.
Worksheets("Print Data").Range("A34:AA34").Borders.LineStyle = xlNone was really me just trying a few things to isolate the issue. I don't really want to use this as then I will have to reestablish all the correct borders in the macro before exiting. But if this is the only solution then that is what I will have to do.
If I Print Preview the file in the normal way it looks fine but if I Print Preview using my macro I get the line indicated above. I have changed the line to a blue colour for ease of viewing. As you will see it is the top border of row 17 that is currently causing the issue. However this will happen with any row that is being collapsed through hiding.

 

[Alex Blakenburg]

I have the issue both viewing it normally and in the Macro as long as I collapse / hide the rows.
I'm afraid I haven't been able to work out how to stop it from happening.

 

[sparky2205]

Ok, thanks for taking a look anyway Alex. Much appreciated.