Selecting a dynamic range of data based off column data

I need to apply a function to a range of data. The data range changes each time I pull the data.

I would like to script a macro that will change the range as the data changes.

But the data I am working with...is a bit complicated.

For example:
I need to apply a function to multiple ranges of data in a worksheet. The row range of data is in between 2 strings that is listed on column B, (i.e. start, end). BUT the columns range is between C-G. I need select the range in column C-G but between the rows listed in column B.

I am having the hardest time figuring out how to select the row range...based off the data in a different column...

Any help will be very helpful.

Thanks.

I have attached the sample data.

So within a particular worksheet I have multiple buildings with cozy, small, medium and Large. (In sample attached no large). I need to select/apply each room type and find the % that is not empty. So in attached i need to select E7:L23, E25:L36, E38:L43.

So I was hoping to create a macro that would search for the string Cozy and Cozy Total and have the in between be the row range. I will know the column ranges.

So I am not sure if my approach is the best way of getting what I want. This data is from a pivot chart...and I've tried different kind of summaries and it isn't give me the outcome I need, such is with life.

Thanks for looking into this, I really appreciate it.

SAMPLE.xlsx

Yes, my data is based off a pivot chart. Thank you for being patient... haha.

I have many buildings listed in a spreadsheet with the room types(cozy,small.medium, large). I am doing a 2 week time span. So I know my column data. But rooms can get added or removed between each time I grab the data. So my row value always changes. I care about 6 hours in the day for 10 work days. If I have 2 cozies in a building there is a total of 120 bookable hours. I need to find how free a type of room is at any given time during the day.

But I am running into 2 issues with the pivot chart. So these are room booking data and the data includes all bookings made within an hour. I don't really care about how often a room gets booked in an hour. I just care that it was booked. So my pivot chart is coming up with 1,2,3 sometimes 4. Which skews the data. Two, seems like the pivot table doesnt want to summaries the data the way I need it. It doesn't give me a break down of each room type and how full/empty it is. It does it for the total building.

I currently go through and manually select each section of data and apply a function to get what I want. But I figure since I do this all the time, I mind as well automate it. Maybe I am just thinking about it all wrong.

I have attached the data and pivot chart.

So as you can see in the pivot charts there is a 1 in the place where a room was book in that hour. But a 2 or 3 would show up if a room is booked more than once. That throws off the data.

I need to answer this question. How likely is one able to find a room, of each room type, in each building in the given hour range.

Thanks Miketron! My head has been spinning around this for weeks.

sample data.xlsb