• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to OMIT blank lines present within data in a cell.

Rahul Agarwal

New Member
I have to copy data present in a cell into word document and if cell is empty NTR should be written in Word File. Using below code I am facing some issue as I have to remove all blank lines present between data in a particular cell, but this code is also pasting blank lines between data.

  • If data to be copied from cell A2 is :
It will be organised near SAC. So, come and register yourself,
Talent can be anything - Singing, Dancing, Fashion, Mimicry

You will get a chance to meet Celebrity influencers from TVF
A shoot team from TVF would also be there to cover the event.

  • In Word File data should get pasted in this way (omitting blank line):
It will be organised near SAC. So, come and register yourself,
Talent can be anything - Singing, Dancing, Fashion, Mimicry
You will get a chance to meet Celebrity influencers from TVF
A shoot team from TVF would also be there to cover the event.

Code:
ActiveDocument.Bookmarks(BM_name).Select

If IsEmpty(sheet.Sheets("Weekly Update").Range(Cell_name).Value) = True Then
        Selection.TypeText Text:="NTR"
    Else
        sheet.Sheets("Weekly Update").Range(Cell_name).Borders.LineStyle = xlNone
        s = Trim(sheet.Sheets("Weekly Update").Range(Cell_name).Value)
        Selection.TypeText Text:=s

    End If
 
Upload a sample file representative of your actual file. In this manner, we can see what your raw data actually looks like. For example, no way of knowing what the line separator is comprised of that is generating the blank line.
 
Upload a sample file representative of your actual file. In this manner, we can see what your raw data actually looks like. For example, no way of knowing what the line separator is comprised of that is generating the blank line.


Any blank line between data present in a cell is not required to get pasted in WORD.
 

Attachments

  • Sample.xlsx
    8.3 KB · Views: 2
Does this help:
Code:
s = Replace(Trim(Sheet.Sheets("Weekly Update").Range(Cell_name).Value), Chr(10) & Chr(10), Chr(10))
 
Does this help:
Code:
s = Replace(Trim(Sheet.Sheets("Weekly Update").Range(Cell_name).Value), Chr(10) & Chr(10), Chr(10))

@Ajesh No this is not working, still there are blank lines coming in WORD document. I just want information present in a cell to be pasted not any blank line.
 
@Ajesh No this is not working, still there are blank lines coming in WORD document. I just want information present in a cell to be pasted not any blank line.
Works good for me for the sample you have provided. See enclosed file for the result I get.

Do you have more than one line breaks between text? IF so, pls can you upload a few more examples of cell values?

Provide both sample files excel and word. i.e. how is it in Excel and what do you expect in Word file.

Thanks/Ajesh
 

Attachments

  • Doc1.docx
    11.4 KB · Views: 1
Back
Top