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.

Merged cells are one of Xl's worst features and their use is not recommended

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

Run where exactly? command prompt?

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

Should I copy your merge code in this description here?

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

Attached Files

withpictures.docx (50.8 KB, 8 views)

I have copied your code in VBA module, but unable to save the code

Try typing


Option Explicit at the top of the vba editor

Look I somehow created it, but should I click "run" to merge cells?

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