none
Excel plots show empty cells as zero value RRS feed

  • Question

  • Hi,

    I am using Microsoft Excel 2013 version and I have made some quality assurance files on it. This quality assurance file uses macros in which one takes a copy from a date and cell values and copies these values to the another sheet. On this another sheet there are plots which should get updated as you perform a macro.

    The problem in this updating process is that when the cell value is empty, as it is in many cases, plot represents this empty value as a value zero value. I have tried many kind of tricks, but nothing seems to help. Now I would like to know how to update plots without drawing empty cells as zero value?

    Monday, February 26, 2018 10:33 AM

All replies

  • Hi,

    Based on your description, I will move your thread to Excel for Developer forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Please provide the code about your problem, so that we can get more accurate solutions to this problem. 

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Tuesday, February 27, 2018 2:57 AM
  • Hello Tallapek,

    I think it depends on your data and your macro. What's your data file and What's the macro code?

    Could you share a simply xlsm file with macro so we could try to use it to reproduce your issue?

    For sharing file, you could share the file via Cloud Storage, such as One Drive, and then put link address here. Thanks for understanding.

    Best Regards,

    Terry



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 8:21 AM
  • Hi Terry,

    I modified my Excel file and you can find it on the link mentioned below:

    https://drive.google.com/file/d/1CcOlNIBcmbPOcWEmOkuRatJbluS-38y3/view?usp=sharing

    First you put some values on the 5.5 section on the Pöytäkirja sheet (date and some values) and then press Tallenna button. After you have pressed Tallenna button, macro takes a copy from the Pöytäkirja sheet and it collects values from section 5.5 and put those values on the Absoluuttinen annos vedessä sheet. As you can see from that sheet macro has been performed on days 2.9.2019-2023 and Test figure has been automatically updated. Values on days 2.9.2019-2023 are, however, empty (column B) but on the figure these values are plotted as a zero value. Why so?

    Hopefully that Google Drive displays my Excel file correctly.

    Br, Pekka

    Saturday, March 3, 2018 10:36 PM
  • Hello Pekka,

    I would suggest you set value directly instead of Copying/Pasting cell.

    Below simply code could work for me to add empty value but won't be plotted. You could adjust for your need.

    Sub Tallenna_55()
        Application.ScreenUpdating = False
        Sheets("Absoluuttinen annos vedess?).Select
        Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("P鰕t鋕irja").Select
        Sheets("Absoluuttinen annos vedess?).Range("A1") = Sheets("P鰕t鋕irja").Range("H260")
        Sheets("Absoluuttinen annos vedess?).Range("B1") = Sheets("P鰕t鋕irja").Range("A1")
        Sheets("P鰕t鋕irja").Select
        ActiveWorkbook.Save
        Range("N257").Select
        Application.ScreenUpdating = True
    End Sub
     

    Note: The page could not figure out the sheet name, remember adjust it while testing.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 5, 2018 9:21 AM