need to remove zeros from the total row.

Posted by: hpadmasola1 on 8 September 2017, 2:06 pm EST

  • Posted 8 September 2017, 2:06 pm EST


     hi,


      I added the below line of code to get the totals row in my spread and it works fine. In the columns that are text, it shows a zero. I want to show blank cell in the place of a zero. I tried to wrap a IF function around the sum but was not successful. How can I achieve this ?


    fpspread1.ActiveSheet.Rows(fpspread1.ActiveSheet.Rows.Count - 1).Formula = "SUM(A1:A" & fpspread1.ActiveSheet.Rows.Count - 1 & ")"


     Thanks


    harish

  • Replied 8 September 2017, 2:06 pm EST

    Hello Harish,

    You should be able to display a blank space instead of a zero in a Cell where the formula is not applied.You may use the code as below,

           FpSpread1.ActiveSheet.RowCount = 10

           FpSpread1.Sheets(0).Rows(9).Formula = "IF(SUM(A1:A9)=0, """", SUM(A1:A9))"

     

    I hope it helps you.

    Thanks,

     

     

  • Replied 8 September 2017, 2:06 pm EST

     


    Great.That worked for string columns. What do you do about dateTime columns ? It shows a date in the totals row. How do I get rid of that ?


    Thanks


    harish

  • Replied 8 September 2017, 2:06 pm EST

    Harish,

    You can check the cell type of column (on which you are applying SUM formula) and if it is of DateTimeCell type, you can set it to Noting, as follows:

    If TypeOf FpSpread1.ActiveSheet.Columns(0).CellType Is FarPoint.Win.Spread.CellType.DateTimeCellType Then
                FpSpread1.ActiveSheet.Cells(7, 0).Formula = Nothing
    End If

    Thanks

     

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels