Trying to sum a column with merged Cells

[Darren Smith]

I am having a very bad day trying to make this work.
Column K has quite a few cells which are merged together I am trying to skip over them but keep coming up against a brick wall.
The excel sheet cannot be altered as it is a template.
Thanks in advance

Private Sub AllocHours()

TurnOff
Dim iRow As Integer
Dim LRow As Long, i As Long
Dim ws As Worksheet
Dim RangeRow As Range, Rng As Range, MyCell As Range, Rrg As Range


 
Set ws = ThisWorkbook.Worksheets("Job Card Master")

Set RangeRow = ws.Range("A13:A" & ws.UsedRange.Rows.Count).Find("TOTAL BODY SHOP HOURS", LookIn:=xlValues, LookAt:=xlWhole)

LRow = ws.Range("A" & Rows.Count).End(xlUp).Row


    If Not RangeRow Is Nothing Then
    iRow = RangeRow.Row
    End If

   If ws.Range("K13:K" & LRow).MergeCells = True Then
   Set Rng = ws.Range("K13:K" & LRow)
  End If
 
   For Each Rng In ws.Range("K13:K" & LRow)
 
   For i = 13 To Rng.Rows.Count
   Set Rrg = Rng.Rows(i)
   Set MyCell = ws.Cells(iRow, 11)
   MyCell.Value = WorksheetFunction.Sum(Range("K13:K" & Rrg.Value))
    Next i
  
Next

TurnOn

End Sub

 

[steve the fish]

Why arent you just saying =SUM(K1:K178) ?

 

[steve the fish]

The answer could be found like this:

Dim RangeRow As Range

With Worksheets("Job Card Master")
    Set RangeRow = .Range("A13:A" & .Rows.Count).Find("TOTAL BODY SHOP HOURS", LookIn:=xlValues, LookAt:=xlWhole)
    If Not RangeRow Is Nothing Then
        .Cells(RangeRow.Row, 11) = Application.Sum(.Range(.Cells(1, 11), .Cells(RangeRow.Row - 1, 11)))
    End If
End With

 

[Darren Smith]

Thanks, work`s like a dream your ace