Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-13-2020, 03:36 AM
kingston123 kingston123 is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2013
Advanced Beginner
How to remove line between "Black" & "device"?
 
Join Date: Sep 2018
Posts: 85
kingston123 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 06-13-2020, 05:07 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to remove line between "Black" & "device"? Windows 7 64bit How to remove line between "Black" & "device"? Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Merged cells are one of Xl's worst features and their use is not recommended
Reply With Quote
  #3  
Old 06-13-2020, 09:54 AM
Purfleet Purfleet is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #4  
Old 06-13-2020, 10:22 PM
kingston123 kingston123 is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2013
Advanced Beginner
How to remove line between "Black" & "device"?
 
Join Date: Sep 2018
Posts: 85
kingston123 is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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

Run where exactly? command prompt?
Reply With Quote
  #5  
Old 06-13-2020, 11:47 PM
Purfleet Purfleet is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #6  
Old 06-14-2020, 07:59 AM
kingston123 kingston123 is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2013
Advanced Beginner
How to remove line between "Black" & "device"?
 
Join Date: Sep 2018
Posts: 85
kingston123 is on a distinguished road
Default

Should I copy your merge code in this description here?



Reply With Quote
  #7  
Old 06-14-2020, 08:13 AM
Purfleet Purfleet is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
File Type: docx withpictures.docx (50.8 KB, 6 views)

Last edited by Purfleet; 06-14-2020 at 08:19 AM. Reason: pictures
Reply With Quote
  #8  
Old 06-14-2020, 08:20 AM
kingston123 kingston123 is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2013
Advanced Beginner
How to remove line between "Black" & "device"?
 
Join Date: Sep 2018
Posts: 85
kingston123 is on a distinguished road
Default

I have copied your code in VBA module, but unable to save the code
Reply With Quote
  #9  
Old 06-14-2020, 08:31 AM
Purfleet Purfleet is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Try typing


Option Explicit at the top of the vba editor


Reply With Quote
  #10  
Old 06-14-2020, 08:36 AM
kingston123 kingston123 is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2013
Advanced Beginner
How to remove line between "Black" & "device"?
 
Join Date: Sep 2018
Posts: 85
kingston123 is on a distinguished road
Default

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



Reply With Quote
  #11  
Old 06-14-2020, 09:15 AM
Purfleet Purfleet is offline How to remove line between "Black" & "device"? Windows 10 How to remove line between "Black" & "device"? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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 remove line between "Black" & "device"? How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft