#1
|
|||
|
|||
How to prevent a cell to disappear
Hi,
I have a macro that copy/paste a table from Excel to Word. The table has 3 columns (last one can be empty) and a variable number of rows Everything works fine except that when the the text in the 2nd column is longer than the size of the cell and the last cell of the row is empty, this cell dispappear and is replaced by the 2nd cell... See attached picture: How can I avoid this? Thanks ;-) |
#2
|
|||
|
|||
Where is your macro?
|
#3
|
|||
|
|||
Select the column B of the used range, right click the selection and select Format Cells Menu, in Alingment enable "Wrap text" option, do this before copy to word.
To insert this procedure into your macro you can do something like this: oRange.columns(2).wraptext=true Backup before test.
__________________
Backup your original file before doing any modification. |
#4
|
|||
|
|||
The "wrap text" option works in Excel but once pasted in Word the cell disappear as you can see on these pictures:
Here's my code: Code:
Sub ExportInvNL() Dim wdApp As Object Dim wd As Object Dim xlSheet As Worksheet Dim rng As Range Dim LastRow As Long Const wdReplaceAll As Long = 2 Const wdFindContinue As Long = 1 On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err.Number <> 0 Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 Set wd = wdApp.Documents.Add("C:\Users\laure\Documents\Boulot\TestInventarisNL.docx") wdApp.Visible = True Set xlSheet = ActiveWorkbook.Sheets("INV") With xlSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set rng = .Range("A2:C" & LastRow) rng.Copy With wd.Range .Collapse Direction:=0 .InsertParagraphAfter .PasteSpecial DataType:=1 With .Find .ClearFormatting .Text = vbTab .Replacement.ClearFormatting .Replacement.Text = " " .Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue End With End With End With End Sub I could put a blank character in column C and that works but I thought maybe there's a more "clean" solution... |
#5
|
|||
|
|||
Try that (backup before doing any modification):
after Set rng statement: Code:
rng.Cells.WrapText = True Code:
.PasteSpecial DataType:=1 Code:
.Paste
__________________
Backup your original file before doing any modification. |
#6
|
|||
|
|||
|
#7
|
|||
|
|||
There's nothing wrong with the pasted table.
I think that you just need to adjust column width. The last cell is empty so it's have a minimum width. Try: Quote:
__________________
Backup your original file before doing any modification. |
#8
|
||||
|
||||
The way I've done this in the past is to change the font size in Excel to something tiny eg 2pt before copying, then after pasting into Word, reset the font size to whatever you wanted to have.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#9
|
|||
|
|||
Thanks eduzs and guessed, I'll try both solutions when I'll be back at work.
I'll let you know... |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow | FUGMAN | Excel Programming | 7 | 02-05-2017 08:37 AM |
Prevent cell from expanding vertically. | snowboarder2 | Word Tables | 1 | 08-24-2016 02:52 PM |
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C | rick10r | Excel | 1 | 07-05-2016 12:07 PM |
Data Validation - Prevent blank cell based on a condition | dawd | Excel | 2 | 08-12-2015 05:44 AM |
Word tables: prevent word from highlighting whole cell when I highlight text | skylark53 | Word | 2 | 08-04-2015 08:12 AM |