sparky2205
Well-known Member
- Joined
- Feb 6, 2013
- Messages
- 507
- Office Version
- 365
- 2016
- Platform
- Windows
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:
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:
into the vba but to no effect.
Does anyone have any ideas?
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:
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:
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
Does anyone have any ideas?