Trying to sum a column with merged Cells

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
So you want to sum anything in column K that isnt in merged cell? You could try this or similar:

VBA Code:
Dim lr As Long, i As Long, rng As Range

lr = Range("K" & Rows.Count).End(xlUp).Row
For i = 1 To lr
    If Range("K" & i).MergeCells = False Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Range("K" & i))
        Else
            Set rng = Range("K" & i)
        End If
    End If
Next
MsgBox Application.Sum(rng)
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows
I`ve done what you said(I think we are on the right track) but it seems to count to 17 rows when there are up to 299 rows. The range is Variable.
For some reason, the rng variable doesn't work? Says: Run Time Error 13

See my code

VBA Code:
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)

iRow = RangeRow.Row

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

For i = 1 To LRow
    If Range("K" & i).MergeCells = False Then
        If Not Rng Is Nothing Then
            Set Rng = Union(Rng, Range("K" & i))
        Else
            Set Rng = Range("K" & i)
        End If
    End If
Next

MsgBox Application.Sum(Rng)

ws.Cells(iRow, 11) = Application.Sum(Rng).Value


End Sub
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
Ok now qualify the ranges. So where you see range in what i gave you add the worksheet. Looks like should be ws.Range. Im not sure why you have changed it to use "A" when you are acting on column "K" for your last row calc.
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I`ve added the WS in but still, no luck, and the last row is a column K. It`s the same as before
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

This line says error 424.
Aso the rng variable says error 13.


VBA Code:
ws.Cells(iRow, 11) = Application.Sum(Rng).Value
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,157
Office Version
  1. 365
Platform
  1. Windows
I think it would be very helpful for us to see an actual example of what you are trying to do (i.e. post a sample of your data and expected results based on that data sample).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
630
Office Version
  1. 2019
Platform
  1. Windows
I will send my workbook to you.
I removed the .Value and it now says 0.5 in the cell should say 17

 

Forum statistics

Threads
1,148,369
Messages
5,746,295
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top