Copy and paste a calculated range - transpose and paste values ignoring "0" formula results.

  • Hi

    I have data that sums across columns based on column header. I need to take these totals and transpose to a single column without the 0 results.

    Currently the code (thanks to another user) results in a blank cell where the formula result is zero.

    How can I transpose the data so that there are no blank (zero) cells in the resulting list? Pls refer screenshot for desired result

    I thought an If > 0 statement might work but I got the same result. Skip blanks is also not applicable as there are results for the formula.

    Many thanks for your help once again.

    J:)

  • Hello,


    If you are only looking for a patch .... i.e adding a test to ignore empty cells ....



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,

    Thank you for the prompt response and for you continued dedication to helping us on this site. Not sure what you meant with looking for a patch?8)

    Anyway, I tried the code but got a Runtime Error - Type Mismatch on line 13..

    Any clues?


    Cheers


    J

  • Hello Jan,


    Attached is your test file ...


    Is it closer to your expected result ?

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad you could fix your problem :)


    Thanks a lot for your Thanks ...AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)