title: Copy Excel range to embedded Word document skipping "" (empty cells)
body: <p>(This question is a follow-up on <a href="https://stackoverflow.com/questions/54445491/excel-vba-copy-range-all-cells-from-the-left-to-embedded-word-document/">how to work with a document embedded in an Excel workbook</a> in the Word application interface (instead of in-place). The reason this is necessary is to be able to save the result as an independent document.)</p>
  
  <p>The problem is that I have dynamic content in Excel. In cells of Column E I have actually formula, for example E55 <code>=IF(B55="";"";"normal")</code> E55 is also <code>=IF('Technical!B55'="";"";'Technical!B55')</code>. My current code does not understand that for example cell B57 is empty and thinks it should be printed to MS Word. It does not insert any content to MS Word but inserts for example bullets that are predefined for style in MS Word. How to stop that? I can make for example <code>=IF(B55="";"empty";"normal")</code> so unnecessary rows will be marked with word "empty" if it helps anyhow.</p>
  
  <pre><code>   A    B                   C                   D                  E
  49    Paragraph with number 1                                    main
  48    Ok text is text and it is good to have here.. a lot of     normal
  50    Legal             John Smith                               table
  51                      Telephone         +4854132155            table 
  52                      Email             john.smith@mail.com    table
  53    Paragraph with number 2                                    main
  54    Text again a lot of text again comes here                  normal
  55    Text again a lot of text again comes here                  normal
  56    Text again a lot of text again comes here                  normal
  57    =IF('Technical!B57'="";"";'Technical!B57')                 =IF(B57="";"";"normal")            
  58    =IF('Technical!B58'="";"";'Technical!B58')                 =IF(B58="";"";"normal")
  </code></pre>
  
  <p>My current code:</p>
  
  <pre><code>With objWord
    Set wdRng = .Range.Characters.Last
    Set wdUndo = .Application.UndoRecord
    wdUndo.StartCustomRecord ("Doc Data")
    Set xlSht = Sheets("Other Data")
    'Here comes Header
    .Bookmarks("Date").Range.Text = xlSht.Range("AT2").Value
    .Bookmarks("DocumentName").Range.Text = xlSht.Range("AX13").Value
  Set xlSht = Nothing
  
  Set xlSht = Sheets("Pricelist")
    For Each cell In xlRng
      wdRng.InsertAfter vbCr &amp; cell.Offset(0, -4).Text
       Select Case LCase(cell.Value)
          Case "title"
            wdRng.Paragraphs.Last.Style = .Styles("Heading 1")
          Case "main"
            wdRng.Paragraphs.Last.Style = .Styles("Heading 2")
          Case "sub"
            wdRng.Paragraphs.Last.Style = .Styles("Heading 3")
          Case "sub-sub"
            wdRng.Paragraphs.Last.Style = .Styles("Heading 4")
          Case "normal"
            wdRng.Paragraphs.Last.Style = .Styles("Normal")
          Case "contact"
            wdRng.Paragraphs.Last.Style = .Styles("Contact")
            Case "attachment"
            wdRng.Paragraphs.Last.Style = .Styles("Attachment")
            Case "technical"
            wdRng.Paragraphs.Last.Style = .Styles("Technical")
            Case "topic"
            wdRng.Paragraphs.Last.Style = .Styles("Topic")
            Case "signature"
  
             Sheets("Signatures").Range("M7:N7").Copy
  
    With wdRng
  
      .Paragraphs.Last.Range.PasteSpecial (wdPasteBitmap)
  
    End With
  
            Case "pagebreak"
       With wdRng
       .Paragraphs.Last.Range.InsertBreak Type:=wdPageBreak
       End With
            Case "table"
  
            xlSht.Range(cell.Offset(0, -4), cell.Offset(0, -1)).Copy
  
    With wdRng
  
      .Paragraphs.Last.Range.PasteAndFormat (wdFormatPlainText)
  
    End With
  
      End Select
    Next cell
    .SaveAs2 ActiveWorkbook.Path &amp; "\" &amp; _
  Sheets("Other Data").Range("AN2").Value &amp; ", " &amp; _
  Sheets("Other Data").Range("AN7").Value &amp; "_" &amp; _
  Sheets("Other Data").Range("AN8").Value &amp; "_" &amp; _
  Sheets("Other Data").Range("AX3").Value &amp; ".docx"
  
    wdUndo.EndCustomRecord
    Set wdUndo = Nothing
    .Undo
    .Application.Quit False
  End With
  </code></pre>