Oct 08 2018 07:47 AM
Hello all - I am trying to figure out how to merge columns in a spreadsheet, under a specified condition.
Hopefully I can explain this clearly.... Situation: I have created a drop down list in column D.... when someone picks specific items in that drop down, I would like the blank cells in columns E, F, & G to automatically merge to one large cell (this is in regards to format, not content merge). However, if other items in the drop down are selected, no merge is to occur. the Merge formatting needs to be unique to each row, as each item is selected. Is this possible?
Oh, I'm using Windows - MO Professional Plus 2016
THANK YOU!
Oct 08 2018 10:46 AM
Hi,
Yes, it's possible!
But you need to inject the worksheet with some lines of VB code to get that done!
I would suggest this code:
Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.DisplayAlerts = False
If Not Intersect(Target, Range("D:D")) Is Nothing Then
If Target.Value = "Home" Or _
Target.Value = "House" Then
Target.Offset(0, 1).Resize(1, 3).Merge
Else
Target.Offset(0, 1).Resize(1, 3).UnMerge
End If
End If
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
To apply this code, please hover the Mouse over the Worksheet tab, right-click, and select View Code.
Then copy and paste it into the worksheet code module.
After that, save the workbook as .xlsm file extension to keep the code saved in it.
But, don't forget to change the Target.Value to make it fits your needs as the below syntax:
If Target.Value = "Specific_Item_1" Or _
Target.Value = "Specific_Item_2" Or _
Target.Value = "Specific_Item_3" Then
Please find an example of this in the attached file
Hope that helps
Oct 08 2018 03:07 PM
OMGosh - you are the BEST. Thank you , it worked beautifully!!!!