VBA Conditional Cell Merge

mamyers64

New Member
Joined
Feb 11, 2016
Messages
17
I need to perform a conditional cell merge. I assume it will require VBA. The data is the result of an external SQL query. The final product in this example is the body of a Work Order Detail. I will only be displaying one WO at a time; user will view and or Print, so it needs to fit on a single sheet (Vertical 8.5*11). The Pervasive SQL table contains a longvarchar field; the data contains carriage returns (char(10)) in order to achieve the desired formatting. When I apply Wrap Text to column G I get the results in the top example. However based on the value in B5 = "N" I want to merge cells D5:G5 and I want to resize the row so I can see all the text and not waste space. The second example is what I want it to look like. Given this is a query results it is impossible to foretell which rows will have the "N" in column B or how many there will be.
[TABLE="width: 1359"]
<tbody>[TR]
[TD][/TD]
[TD] A
[/TD]
[TD] B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[TD] F
[/TD]
[TD] G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD] Line
[/TD]
[TD] LMO
[/TD]
[TD] PART_WC_OUTSIDE
[/TD]
[TD] Set_Up
[/TD]
[TD] Run_Time/Qty
[/TD]
[TD] Line_Desc
[/TD]
[TD]Subset_Spec
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD] 000100
[/TD]
[TD] M
[/TD]
[TD] 407
[/TD]
[TD="align: right"]0.0012
[/TD]
[TD="align: right"]0.0004
[/TD]
[TD] 70a NBR-SULFUR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD] 000101
[/TD]
[TD] C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] MS-407 REV.A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD] 000150
[/TD]
[TD] L
[/TD]
[TD] MOLD
[/TD]
[TD="align: right"]0.033
[/TD]
[TD="align: right"][/TD]
[TD] MOLD PULL & CLEAN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD] 000158
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DM_File_Name: SQS-00028.pdf DM_Description: NPT QUALITY STAND DM_From_Date: 2006-06-01
DM_Rev: C DM_Status: A DM_Type: :Cust Spec DM_Customer: Myers Ent DM_To_Date:

4.0 INSPECTION METHODS AND REQUIREMENTS

4.1.Functional Thread Size: Acceptance of thread size is determined from the measured standoff
ANPT gage plugs and rings. Standoffis to be measured with a suitable depth indicator to the .
Counting of turns is to be avoided unless linear measurement is impractical. Gage standoff,
is to be added to the measured standoff of the gaged part to derive the true standoff value.
NOTE: Variable-type gaging may be used as an analytical tool for process control but
to determine final acceptance.
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 830"]
<tbody>[TR]
[TD][/TD]
[TD] A
[/TD]
[TD] B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[TD] F
[/TD]
[TD] G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Line
[/TD]
[TD]LMO
[/TD]
[TD] PART_WC_OUTSIDE
[/TD]
[TD] Set_Up
[/TD]
[TD] Run_Time/Qty
[/TD]
[TD] Line_Desc
[/TD]
[TD]Subset_Spec
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]000100
[/TD]
[TD]M
[/TD]
[TD] 407
[/TD]
[TD="align: right"]0.0012
[/TD]
[TD="align: right"]0.0004
[/TD]
[TD] 70a NBR-SULFUR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]000101
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] MS-407 REV.A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]000150
[/TD]
[TD]L
[/TD]
[TD] MOLD
[/TD]
[TD="align: right"]0.033
[/TD]
[TD="align: right"][/TD]
[TD] MOLD PULL & CLEAN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]000158
[/TD]
[TD]N
[/TD]
[TD="colspan: 5"]DM_File_Name: SQS-00028.pdf DM_Description: NPT QUALITY STANDARD DM_From_Date: 2006-06-01
DM_Rev: C DM_Status: A DM_Type: :Cust Spec DM_Customer: Myers Enterprise DM_To_Date:

4.0 INSPECTION METHODS AND REQUIREMENTS

4.1.Functional Thread Size: Acceptance of thread size is determined from the measured
ANPT gage plugs and rings. Standoffis to be measured with a suitable depth indicator to the
Counting of turns is to be avoided unless linear measurement is impractical. Gage standoff, per
is to be added to the measured standoff of the gaged part to derive the true standoff value.
NOTE: Variable-type gaging may be used as an analytical tool for process control but is not
to determine final acceptance.
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I've concluded it can't be done, at least not for free on a message board. This is due to the data dynamic nature, not knowing where the range of cell with be that need merging. So, I created a workaround/better report design so I can still accomplish the objective.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top