Removing borders when printing using vba

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
474
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi folks,
I have a spreadsheet which looks like this:
1661339894417.png

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

I use the following vba to collapse unused rows and print out a compact report, as above.
VBA:
VBA Code:
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:
1661340500771.png

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:
Rich (BB code):
Worksheets("Print Data").Range("A34:AA34").Borders.LineStyle = xlNone
into the vba but to no effect.

Does anyone have any ideas?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top