Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-822091%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20trying%20to%20automate%20formatting%20of%20data%20-%20merge%20cells%20of%20with%20varying%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822091%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%20merged%20cells%20cause%20a%20lot%20of%20problems%20and%20should%20be%20avoided%20wherever%20possible.%26nbsp%3B%20Maybe%20it's%20possible%20to%20find%20a%20different%20approach.%20Why%20to%20you%20want%20the%20text%20in%20column%20A%20and%20the%20values%20in%20E%20and%20F%20centered%20horizontally%3F%26nbsp%3B%20Is%20it%20aesthetics%20or%20does%20it%20have%20a%20functional%20reason%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822209%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20trying%20to%20automate%20formatting%20of%20data%20-%20merge%20cells%20of%20with%20varying%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20it's%20for%20aesthetics.%20The%20merged%20and%20centered%20cells%20become%20the%20final%20readout%20for%20a%20report%20and%20look%20good%20presentation-wise.%20Column%20A%20is%20the%20topic%20of%20the%20chart.%20Columns%20B%20and%20C%20readout%20data%20individual%20pieces%20of%20data.%20Columns%20E%20and%20F%20are%20averages%20and%20standard%20deviations%20of%20the%206%20pieces%20of%20data.%20There%20are%20lines%20of%20code%20and%20sheets%20of%20data%20that%20get%20to%20this%20final%20readout.%20And%20its%20not%20just%201%20this%20one%20case...i'll%20have%20to%20make%20this%20code%20work%20for%2C%20like%2C%2020%20of%20these%20tables...so%20I'd%20like%20to%20automate%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822033%22%20slang%3D%22en-US%22%3EVBA%20-%20trying%20to%20automate%20formatting%20of%20data%20-%20merge%20cells%20of%20varying%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822033%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20automate%20formatting%20of%20some%20data%20that%20I%20make.%20Basically%2C%20if%20there%20is%206%20rows%20of%20data%2C%20then%20I%20need%20to%20merge%20cells%201%20to%206%20in%20columns%201%2C5%2C%20and%206.%20But%2C%20the%20next%20data%20set%20might%20have%208%20rows%20or%20data.%20An%20example%20of%20a%20data%20set....and%20what%20I%20want%20it%20to%20look%20like%20is%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20have%20some%20sample%20code%20that%20might%20be%20a%20starting%20point.%20Cells%201%2C3%2C%20and%205%20will%20always%20be%20set%20(i.e.%20the%20top%20of%20the%20range)%20but%202%2C4%2C%20and%206%20need%20to%20vary%20depending%20on%20the%20data%20set.%20I%20imagine%20that%20a%20countif%20or%20counta%20could%20be%20used%20to%20look%20at%20a%20range%20of%20cells%20(i.e.%20B2-B30%2C%20this%20is%20the%20column%20that%20will%20set%20the%20last%20cell%20in%20all%20cases).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20don't%20know%20how%20to%20save%20the%20number%20from%20a%20countif%20statement%20and%20then%20insert%20it%20into%20the%20rest%20of%20the%20code%20(i.e.%20where%20I%20have%20an%20%22x%22.%20Any%20help%20would%20be%20great.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20formattingfortables()%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESet%20Cell1%20%3D%20Cells(Row1%2C%20Col1)%3CBR%20%2F%3ESet%20Cell2%20%3D%20Cells(Rowx%2C%20Col1)%3CBR%20%2F%3ESet%20Cell3%20%3D%20Cells(Row1%2C%20Col5)%3CBR%20%2F%3ESet%20Cell4%20%3D%20Cells(Rowx%2C%20Col5)%3CBR%20%2F%3ESet%20Cell5%20%3D%20Cells(Row1%2C%20Col6)%3CBR%20%2F%3ESet%20Cell6%20%3D%20Cells(Rowx%2C%20Col6)%3C%2FP%3E%3CP%3ERange(Cell1%2C%20Cell2).Merge%3CBR%20%2F%3ERange(Cell3%2C%20Cell4).Merge%3CBR%20%2F%3ERange(Cell5%2C%20Cell6).Merge%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-822033%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
gms4b
Contributor

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 Replies

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?

@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. 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies