VBA help - removing rows with no values

[Jasesair]

I have a range of data in Excel that I'm filtering (a named range that's actually B34:J52), then pasting into a formatted area in Excel (B9:A52), before copying this range (B9:A52) in MS Word. The range that's been filtered and pasted cannot be changed but potentially there are rows with no values at all. I'd love a line of code that recognises there are no values, resulting in only the required rows being copied in MS Word. I'm hoping this makes sense!!!

I really don't know where the line of code should go so I'll share my whole module. Hoping that's alright! Any help would be very much appreciated.

Sub CreateMarkingGuide1() 'UPDATE

Application.ScreenUpdating = False
Sheets("Marking Guides (2)").Visible = True

Call CopyPasteMGuide_Y3U1 'UPDATE
Call ExcelRangeToWordv21 'UPDATE

Sheets("Marking Guides (2)").Visible = False

End Sub

Sub FilterOutBlanks1() 'UPDATE

ActiveWorkbook.Sheets("Marking Guides (2)").Range("Y3U1").AutoFilter Field:=(2), Criteria1:="<>" 'UPDATE

End Sub
Sub CopyPasteMGuide_Y3U1() 'UPDATE

ThisWorkbook.Worksheets("Marking Guides (2)").Select
Range("b9:j25").ClearContents 'UPDATE

Call FilterOutBlanks1 'UPDATE

Range("b35:j52").Copy 'UPDATE
Range("b9").PasteSpecial Paste:=xlPasteValues 'UPDATE
Range("a9:a25").EntireRow.AutoFit 'UPDATE

Range("Y3U1").AutoFilter Field:=(2) 'UPDATE
Range("d9:d25").ClearContents 'UPDATE



End Sub
Sub ExcelRangeToWordv21() 'UPDATE

Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table

'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False

'Copy Range from Excel
  Set tbl = ThisWorkbook.Worksheets("Marking Guides (2)").Range("b9:j25") 'UPDATE
  Set Header = ThisWorkbook.Worksheets("Marking Guides (2)").Range("b1:j7") 'UPDATE
  Set Sheet = ThisWorkbook.Worksheets("Marking Guides (2)")
'If MS Word is already open
' Set WordApp = GetObject("Word.Application")

'If MS Word is not already open then open MS Word
  If WordApp Is Nothing Then Set WordApp = CreateObject("Word.Application")

'Make MS Word Visible and Active
  WordApp.Visible = True


'Create a New Document
  Set myDoc = WordApp.Documents.Add

'Copy Header range
  Sheet.Select
  Header.Select
  Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

'Set Word Margins
With WordApp.ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = CentimetersToPoints(0.5)
.BottomMargin = CentimetersToPoints(1)
.LeftMargin = CentimetersToPoints(1)
.RightMargin = CentimetersToPoints(1)
End With

'Change the view to header & footer
If WordApp.ActiveWindow.View.SplitSpecial <> wdPaneNone Then
WordApp.ActiveWindow.Panes(2).Close
End If

'Select the Header range and paste as image
WordApp.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
WordApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
        Placement:=wdInLine, DisplayAsIcon:=False

  
WordApp.ActiveWindow.View.Type = wdNormalView
WordApp.ActiveWindow.View.Type = wdPrintView


  'Copy Excel Table range
  Sheet.Select
  tbl.Copy

'Paste Table into Word
myDoc.Content.Paste



'Autofit Table so it fits inside Word Document
Set WordTable = myDoc.Tables(1)
WordTable.AutoFitBehavior (wdAutoFitWindow)


Application.ScreenUpdating = True
  Application.EnableEvents = True

'Clear The Clipboard
  Application.CutCopyMode = False
 
  ThisWorkbook.Worksheets("Class Setup").Select

   WordApp.Activate


End Sub

 

[JLGWhiz]

As your name suggests, you really are a Whiz!!! Thanks so much - this worked perfectly. Really do appreciate your time.

You're welcome,
regards, JLG