VBA - trying to automate formatting of data - merge cells of varying number of rows

[gms4b]

Hi, 

I'm trying to automate formatting of some data that I make. Basically, if there is 6 rows of data, then I need to merge cells 1 to 6 in columns 1,5, and 6. But, the next data set might have 8 rows or data. An example of a data set....and what I want it to look like is below. 

I also have some sample code that might be a starting point. Cells 1,3, and 5 will always be set (i.e. the top of the range) but 2,4, and 6 need to vary depending on the data set. I imagine that a countif or counta could be used to look at a range of cells (i.e. B2-B30, this is the column that will set the last cell in all cases). 

I just don't know how to save the number from a countif statement and then insert it into the rest of the code (i.e. where I have an "x". Any help would be great.

Thanks,

Greg

Sub formattingfortables()

Set Cell1 = Cells(Row1, Col1)
Set Cell2 = Cells(Rowx, Col1)
Set Cell3 = Cells(Row1, Col5)
Set Cell4 = Cells(Rowx, Col5)
Set Cell5 = Cells(Row1, Col6)
Set Cell6 = Cells(Rowx, Col6)

Range(Cell1, Cell2).Merge
Range(Cell3, Cell4).Merge
Range(Cell5, Cell6).Merge

End Sub

[#2]

Hello @gms4b ,  merged cells cause a lot of problems and should be avoided wherever possible.  Maybe it's possible to find a different approach. Why to you want the text in column A and the values in E and F centered horizontally?  Is it aesthetics or does it have a functional reason?

[#3]

@Ingeborg Hawighorst 

Yes, it's for aesthetics. The merged and centered cells become the final readout for a report and look good presentation-wise. Column A is the topic of the chart. Columns B and C readout data individual pieces of data. Columns E and F are averages and standard deviations of the 6 pieces of data. There are lines of code and sheets of data that get to this final readout. And its not just 1 this one case...i'll have to make this code work for, like, 20 of these tables...so I'd like to automate it.