excel - How to do sum at 2 cell after the last used cell in column and column has blank cells in between?

Question

Get Image Here

As shown in picture, I have two columns J and K to do sum at two cells after last used cell, Problem is there are sometimes randomly oriented empty cells due to which sum stops in between.

Also, the sum cell of J column should have Green text and the sum cell of K column should have Red text. Plz include this into your code, I don't know how to do it.

I am beginner in vba, it's just my fifth day...

Here is what I have tried till now...

At first, I thought it will be easy to do sum, just finding last used cell in column and then applying sum formula. So I tried the code below...

 Dim LastRow As Long
 
 LastRow = Range("J2").End(xlDown).Row
 Cells(LastRow + 2, "J").Formula = "=SUM(J2:J" & LastRow & ")"

Then I realised that it's not gonna be that easy in my case. What I found was when I played macro, the sum was done, but only in between the cells. So, I tried all my programming knowledge and thought to apply condition that if cell contains value then add that value to variable and if cell is empty then move to the next cell... But I don't have idea how to do it... Still I am providing the code which I tried...

Sub SumData()
 Dim sumOne As Long
  Dim LastRow As Long

 LastRow = Range("F2").End(xlDown).Row
 sumOne = 0
 
        Dim MyRng As Range
        Set MyRng = ActiveSheet.UsedRange.Columns("F")
        Dim cellRng As Range
Sum:
        For Each cellRng In MyRng.Cells
             If cellRng.Value <> "" Then
            sumOne = sumOne + cellRng.Value
            
            Cells(LastRow + 2, "F").Value = sumOne
        
            GoTo Sum
        End If
            Range("A2").Select
        Next
        
End Sub

With the codes above I was trying to practice with data in F column so column F is not problem... And I think there would be easier and better way to do it

Answer: Issue guided by Simon : To find the last row you were using xlDown. This means it searches from row 1 down until it finds an empty cell. You need to use xlUp so it searches from the bottom of the sheet upwards so it finds the true last cell.

Answer 1

Have a go with this and see if it's what you're after.

Sub SumColumns()

Dim lRowJ As Long, lRowK As Long

lRowJ = Range("J" & Rows.Count).End(xlUp).Row
lRowK = Range("K" & Rows.Count).End(xlUp).Row

With Range("J" & lRowJ + 2)
    .Value = Application.Sum(Range("J1:J" & lRowJ))
    .Font.Color = vbGreen
End With
With Range("K" & lRowK + 2)
    .Value = Application.Sum(Range("K1:K" & lRowK))
    .Font.Color = vbRed
End With

End Sub

Here's another example using a specific sheet name, without the With and using RGB for the colour:

Dim lRowJ As Long, lRowK As Long, ws As Worksheet

Set ws = Sheets("Sheet1") 'Name of your sheet
lRowJ = ws.Range("J" & Rows.Count).End(xlUp).Row
lRowK = ws.Range("K" & Rows.Count).End(xlUp).Row

ws.Range("J" & lRowJ + 2).Value = Application.Sum(Range("J1:J" & lRowJ))
ws.Range("J" & lRowJ + 2).Font.Color = RGB(0, 150, 0)
    
ws.Range("K" & lRowK + 2).Value = Application.Sum(Range("K1:K" & lRowK))
ws.Range("K" & lRowK + 2).Font.Color = RGB(255, 0, 0)