VBA to remove rows based on column colour

[Robert1290]

Hi there,

I have attached an example spreadsheet which I could do with some help on please.

I am trying to acheive the following:-

caluclating the average of each column zz, zy, zx & zw. I want to caluclate this within a section.

The section is determined by 2 rows of blue. Where there is 1 row of blue this can be disregarded.

Ideally, each time there is a double row blue, this is a new section, which would have a section name.

What would be perfect, is if there was a way to return a result which just has secion name, and the 4 averages for each section.

I hope this all makes sense.

Rob

[#2 · Accepted solution]

@Robert1290 

What you're asking makes sense. What you have as a spreadsheet, however, raises a number of questions.

I've given you a solution. But I made some modifications to the way you arrayed your data, AND to the nature of the data. 

1. Instead of separating sections by headers, I identified the sections by a new column containing the differentiating label.
2. And instead of texts that look like numbers but aren't, I used the VALUE function to convert them to values. It's a lot easier to calculate averages (well, for that matter, to do ANY kind of calculation) if you actually use numbers. I'm not sure what the source is for these, but I left them as is and just created adjacent columns with the text converted to value.

The solution I offered uses a new function, FILTER, to select for the calculation only the rows that meet the criterion of belonging to the designated group. Here's the formula:

=AVERAGE(FILTER(G$3:G$47,$A$3:$A$47=$M3))

Where $G3:$G47 is the column containing the values for ZZ (I designated it as ZZ2)

$A$3:$A$47 is the column containing the group designation

$M3 is the cell containing the criterion for the filter......

You do need the most current version of Excel for FILTER to function. If you don't have that, I recommend updating your system. This is only one of a number of really useful new functions.

[#3]

Hi Mathetes,

Many thanks for taking the time to look at this for me and help. This is awesome and is doing exactly what I need it to do.

I million and 1 thank you's.

Best regards

Rob

[#4]

@Robert1290 

You're very welcome.

I'm glad my response made sense and that you're able to use FILTER.

For further education on it and other of what are called "Dynamic Array Functions" you might find this video helpful. It'll take close to 30 minutes to view, but will introduce you to some very useful and powerful functions, FILTER being just one of them. https://www.youtube.com/watch?v=9I9DtFOVPIg