Determine the column range of merged cells

Question

My goal is to average a number of columns in one row.

The table is laId out by month, ONE week per column and a number of rows with the numbers in each column.

The table is divided into months across the top, with the month name header in a merged cell of four or five columns depending on how many weeks the month spans (Saturdays and Sundays are omitted at the start and end of the month leading to the four or five weeks per month).

For example, November this year (2015) includes five work weeks. The range of the header is O11:S11 - a merged cell.

Above this table that spans the year, there is a cell to average the number of hours spend on project time versus overhead.

In my example for November, I would average O43:S43. I would like to type or select the month to average in one cell, then find the column range of that month from the merged cells holding November.

For example, if the key cell is D9, holding November, and I want to place the average value in E9, and the row of months for the year spans from K11 - W11, what are the possible approaches to solving this question?

I tried using an INDEX MATCH formula but that gets me nowhere. I need to write a VBA function (I think).

not sure i really understand the question, but any time I want to use formulas, I avoid using merged cells. What you can do is insert a row between the weeks and the merged month cell, and put the month above each week (i.e. you would have 5 cells with the month 'November' above the work weeks). Then you can use SUMIF, AVERAGEIF, etc — Jaycal
As Jaycal said - I avoid merged cells. Centre across selection cells are easier to work with. You can check if a cell is part of a merged range with, for example, Debug.Print Sheet1.Range("A1").MergeCells - will return TRUE/FALSE and find the range of merge cells with Debug.Print Sheet1.Range("A1").MergeArea.Address — Darren Bartrup-Cook
Agreed with these comments - any solution available is going to be more complex than restructuring your workbook to avoid merged cells. — Grade 'Eh' Bacon

Davesexcel Davesexcel · Accepted Answer · 2015-11-02T17:29:41

You can find the merged cell then resize the range.Remove the rng.Selectline, it's just there to check if it is calculating correctly.

 Sub Button1_Click()
    Dim Fm As Range, Av As Range, rng As Range
    Dim s As String, x

    s = Range("D9").Value
    Set Fm = Rows(11).Find(s, LookIn:=xlValues)

    If Not Fm Is Nothing Then
        Set Av = Fm.MergeArea
        Set rng = Av.Offset(32).Resize(, Av.Columns.Count)
        rng.Select    'remove later
        x = Application.WorksheetFunction.Average(rng)
        Range("E9").Value = x
    End If

End Sub

Determine the column range of merged cells

1 Answers