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.
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)
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?
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.