Forum Discussion

Yan_Woellhaf's avatar
Yan_Woellhaf
Copper Contributor
Dec 07, 2023
Solved

Object appears only on printout

This downward-pointing blue arrow, about 2 inches long and 1/8 inch wide appears only on the printout.

 

 

I have been unable to prevent its appearance. I will be very grateful to learn how to remove it. Thanks!

  • djclements's avatar
    djclements
    Dec 11, 2023

    Yan_Woellhaf Based on what you've described, the most logical explanation is the VBA code is using the Range.Copy method to transfer the data to the hidden sheets. For example:

     

    Sub CopyPasteAll()
        Sheet1.Range("A1:C15").Copy Sheet2.Range("A1")
        Application.CutCopyMode = False
    End Sub

     

    When you copy/paste a range, it will include any shapes present in that range; furthermore, any subsequent copy/paste procedures will not overwrite shapes in the destination range (they will persist). At some point, a user inserted the blue arrow into their input sheet, then ran the VBA code, which transferred the arrows to the hidden sheets, and they've been there ever since.

     

    Consider an alternative method for transferring the data. For example, use the Range.PasteSpecial method to paste values only:

     

    Sub CopyPasteSpecial()
        Sheet1.Range("A1:C15").Copy
        Sheet2.Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End Sub

     

    A more efficient method, though, would be to copy the source range to an array, then output the values to the destination range:

     

    Sub CopyToFromArray()
        Dim arr As Variant
        arr = Sheet1.Range("A1:C15").Value
        Sheet2.Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End Sub

     

    Without seeing your code, this is just my "best guess". Hopefully it applies to your situation and can be adapted to meet your needs. If not, then please disregard. Cheers!

12 Replies

  • djclements's avatar
    djclements
    Silver Contributor

    Yan_Woellhaf You can try to locate the shape using the Selection Pane. On the Ribbon, go to Home > Find & Select > Selection Pane... If the shape is present in the worksheet, it will be listed here. Click the applicable "Arrow: Down 1" shape to select it:

     

     

    To hide the shape from the printout, go to the Shape Format tab on the Ribbon and click on the Size and Properties dialog box link:

     

     

    Expand the Properties section and uncheck the "Print object" check box.

     

    • Yan_Woellhaf's avatar
      Yan_Woellhaf
      Copper Contributor

      djclements Thank you very much for taking the time create a very detailed response!

      Unfortunately, my Selection pane is blank.

      • djclements's avatar
        djclements
        Silver Contributor

        Yan_Woellhaf That's an interesting predicament indeed! Is there any chance an image has been inserted into the header or footer? If you switch to page layout view (on the Ribbon, go to View > Page Layout), does the arrow appear? If you click into the header or footer section while in page layout view, do you see the code &[Picture]?

Resources