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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies