IF Statement Added to VBA Code to Display Blank if Sum Equals 0

[lamarh755]

I have 4 columns in an excel spreadsheet where the values in each column are added up at the end of the last row. When the sheet is blank, the totals at the bottom show 0. I am trying to have a formula (possibly an IF statement) added to my current formula to make it to where the 4 cells at the bottom of the sheet show blank if the sum equals 0. I tried to use an IF statement but it errors out because I am not sure how to do it using LastRow. Any help would be greatly appreciated.


Dim aLastRow As Long
aLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row - 3
Range("B" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"
Range("E" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"
Range("J" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"
Range("M" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"

 

[Joe4]

What does aLastRow return when the sheet is blank?
In this situation, what row do you want these formulas which total to 0 to be placed in?

 

[lamarh755]

What does aLastRow return when the sheet is blank?
In this situation, what row do you want these formulas which total to 0 to be placed in?

The sheet is never blank, as there is a header on the template. I probably could have used a better formula/code to be more precise, but I didn't know how to write it. The data that populates starts at row 17 down. Everything above row 17 is just header information. The data goes in the 3rd row up from the last row. The row number changes depending on the data that I select which initiates the macro/code.

 

[Joe4]

OK, but you still did not answer my questions.

However you define "blank" (it doesn't really matter to me), I just need to know the answers to those questions.
In the situation in which it meets your definition of blank, what number exactly does this code return when you run it?

Sub Test()
    Dim aLastRow As Long
    aLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row - 3
    MsgBox aLastRow
End Sub

And on what run do you want this SUM function to should return zero?

And what would typically be the first row number in your sum function (if the first 17 rows are headers, then do the numbers you want to sum typically start on row 18, if there are any to sum)?

 

[lamarh755]

OK, but you still did not answer my questions.

However you define "blank" (it doesn't really matter to me), I just need to know the answers to those questions.
In the situation in which it meets your definition of blank, what number exactly does this code return when you run it?

Sub Test()
    Dim aLastRow As Long
    aLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row - 3
    MsgBox aLastRow
End Sub

And on what run do you want this SUM function to should return zero?

And what would typically be the first row number in your sum function (if the first 17 rows are headers, then do the numbers you want to sum typically start on row 18, if there are any to sum)?

The code currently returns a sum of 0 in aLastRow when the cells above are blank/empty.
I would like for it to show blank/empty until the value does not equal 0.
The first 16 rows are headers. The first row number in my sum function starts at row 17.

Sorry for the confusion.

 

[Joe4]

Does this do what you want?

Dim aLastRow As Long
aLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row - 3

If aLastRow < 17 Then
    Range("B17") = 0
    Range("E17") = 0
    Range("J17") = 0
    Range("M17") = 0
Else
    Range("B" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"
    Range("E" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"
    Range("J" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"
    Range("M" & aLastRow + 1).FormulaR1C1 = "=SUM(R[-" & aLastRow & "]C:R[-1]C)"
End If

 

[lamarh755]

No, that code gave an error message. If that code was to work, it would still not be what I need. I need the formulas that I originally posted to always calculate, even after the macro is ran. I just need the field to turn blank if the sum value is 0 at any given point, meaning once I start entering numbers in the rows above, the field changes from blank to the actual sum value.

I have uploaded a couple of images that will hopefully fully explain. The first image (Excel.JPG) shows the 0's in the Total Results Row. The second image (Excel2.JPG) shows the actual totals once values have been entered on the sheet.

 

[Joe4]

OK, if that is all you want to do, why not just using a Custom format to hide the 0 values?
See: Display or hide zero values - Microsoft Support.

 

[lamarh755]

OK, if that is all you want to do, why not just using a Custom format to hide the 0 values?
See: Display or hide zero values - Microsoft Support.

This did the trick, thank you!!! That solution never crossed my mind.

 

[Peter_SSs]

This did the trick, thank you!!! That solution never crossed my mind.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.