#1
|
|||
|
|||
How to remove line between "Black" & "device"?
I want "Black" & "device" into one cell.
How to remove line between "Black" & "device"? If I click merge then this error message pops up: "Merging cells only keeps the upper-left cell value, and discards the other values", If I click "OK" then "device" text is getting discard. |
#2
|
||||
|
||||
Merged cells are one of Xl's worst features and their use is not recommended
|
#3
|
|||
|
|||
Agreed - merged cells are the devils own work and should be avoided
You could do =C4&CHAR(10)&C5 and turn on Wrap text which is much nicer But if you insist on using merged cells you can select the top cell of the 2 you want merged and run this Sub MergeCellAndBelow() Dim r As Range Dim c1 As String Dim c2 As String c1 = ActiveCell.Value c2 = ActiveCell.Offset(1, 0).Value Application.DisplayAlerts = False Range(ActiveCell.Address & ":" & ActiveCell.Offset(1, 0).Address).Merge Application.DisplayAlerts = True With ActiveCell .Value = c1 & Chr(10) & c2 .WrapText = True End With End Sub Its a bit rough and ready but should give you the idea Last edited by Purfleet; 06-13-2020 at 09:57 AM. Reason: typo |
#4
|
|||
|
|||
Quote:
Run where exactly? command prompt? |
#5
|
|||
|
|||
No in a VBA module.
Best to add to your Personal Macro Workbook if you are going to use it. This explain how to create the Personal Macro workbook https://www.myonlinetraininghub.com/...-personal-xlsb Once done, copy that code into a module and create a shortcut button on your quick access toolbar. Then when yoiu want to merge 2 cells select the top one, click the button and it will be meged (only works on 2 cells vertically - but possible to improve) Purfleet |
#6
|
|||
|
|||
Should I copy your merge code in this description here?
|
#7
|
|||
|
|||
No, records the first macro as per the link in the previous post.
Then press ALT + F11 this will open the vba editor In the left pane you should see VBAProject(PERSONAL.XLSB) Right click on the module folder and insert module Then in the right pane paste in the code Once that is done you will be best off to create a button in your quick access toolbar for ease of use Let me know how you get on Last edited by Purfleet; 06-14-2020 at 08:19 AM. Reason: pictures |
#8
|
|||
|
|||
I have copied your code in VBA module, but unable to save the code
|
#9
|
|||
|
|||
Try typing
Option Explicit at the top of the vba editor |
#10
|
|||
|
|||
Look I somehow created it, but should I click "run" to merge cells?
|
#11
|
|||
|
|||
I assume 'merge' is the one you recorded?
On your workbook select the top cell with Black (device should be in the cell below it) the in the VBA editor Select 'MergeCellAndBelow' and run it. Its coded to only work like that 2 cells one of top of the other, nothing more fancy. As i have said before add it to your QAT for ease of use |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
"Magic" indentation for "too-long" text-lines that are longer than one line | pGrnd | Word | 2 | 04-20-2020 03:23 PM |
Combine "sort within groups" and "identify duplicate/unique values" in two different variables. | civilcervant | Excel | 3 | 06-12-2017 07:27 AM |
Replace "comma" with "and" in line of text | audioman | Word VBA | 7 | 01-21-2016 03:11 PM |
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas | jocke321 | Word VBA | 2 | 12-10-2014 11:27 AM |
How to choose a "List" for certain "Heading" from "Modify" tool? | Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |