Excel - Hiding last picture in row of invoice

Question

I have built an Invoice worksheet that has a top portion (A1:K19) that will remain static; this is the top portion of the invoice. Below Row 19 starts the individual item lines that will be populated with what items are ordered.

I have created a macro for the user that will automatically format the invoice into a 1 page PDF and autohide the unused line item rows; however I have pictures in each line item line.

When I run my macro it hides all the pictures expect the last one. How can I hide the last image box that just sticks out and doesn't hide with row. If I individually hide that row alone it hides, but with a group it does not.

Below is the current VBA script I have written.

Sub Save_Quote_As_PDF()

Application.ScreenUpdating = False

For i = 20 To 59
If ActiveSheet.Cells(i, 3) = "" Then
ActiveSheet.Cells(i, 3).EntireRow.Hidden = True
End If
Next i

Dim PdfFilename As Variant

PdfFilename = Application.GetSaveAsFilename( _
    InitialFileName:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("N2").Value, _
    FileFilter:="PDF, *.pdf", _
    Title:="Save As PDF")

If PdfFilename <> False Then

    With ActiveSheet.PageSetup
        .Orientation = xlPortrait
        .PrintArea = "$A$1:$K$78"
        .PrintTitleRows = ActiveSheet.Rows(19).Address
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
    

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=PdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

End If

  For i = 20 To 59
If ActiveSheet.Cells(i, 3) = "" Then
ActiveSheet.Cells(i, 3).EntireRow.Hidden = False
End If
Next i
                                
 Application.ScreenUpdating = True
                                
End Sub

Here is a screenshot of the pdf section that shows the pic object showing

Screenshot of Image Not Hiding in PDF results

What's the last row that you have line items in? Is it possible your loop that's hiding is off by 1? — Jimmy Smith, Jul 15 '16 at 18:06
The last row of line items that can be populated is row 59, which is not populated as of right now. If I populate all but 3 or 4 rows then execute the VBA code it will work perfectly, it seems to only occur with more than 4 or more rows emtpy. — Stackexplorer, Jul 15 '16 at 18:08
I've added a screenshot of the results of running the macro. — Stackexplorer, Jul 15 '16 at 18:23
I tested it out with some data in cell C(3) and little images in cell E and then I remembered... the images are overlaying the cells and are not really considered inside them, if this makes sense? See my answer below and test it out. — Jimmy Smith, Jul 15 '16 at 18:31

score 1 · Accepted Answer · answered Jul 15 '16 at 18:29

I recreated your code above with my own sample data. The issue seems to be when the images don't have "move and size with cells" (in their properties).

If you have code elsewhere, that's inserting those images then you'll need to do this,

Sub Move_Size() 
    Dim i As Long 
    For i = 1 To ActiveSheet.Shapes.Count
       On Error Resume Next 
       With ActiveSheet.Shapes.Item(i)
            .Placement = xlMoveAndSize 
            .PrintObject = True 
        End With 
    Next i 
    On Error Goto 0 
End Sub

Taken from here

Excel - Hiding last picture in row of invoice

Screenshot of Image Not Hiding in PDF results

1 Answers1