Excel VBA Macro: User Defined Type Not Defined

I'm getting the above error when trying to execute this macros. I'm pretty new to Macros and coding in general so please forgive the ignorance.

Sub DeleteEmptyRows()
Dim oTable As Table, oRow As Row, _
TextInRow As Boolean, i As Long
Application.ScreenUpdating = False
For Each oTable In ActiveDocument.Tables For Each oRow In oTable.Rows TextInRow = False For i = 2 To oRow.Cells.Count If Len(oRow.Cells(i).Range.Text) > 2 Then 'end of cell marker is actually 2 characters TextInRow = True Exit For End If Next If TextInRow = False Then oRow.Delete End If Next
Next
Application.ScreenUpdating = True
End Sub

3 Answers

Your error is caused by these:

Dim oTable As Table, oRow As Row,

These types, Table and Row are not variable types native to Excel. You can resolve this in one of two ways:

1. Include a reference to the Microsoft Word object model. Do this from Tools | References, then add reference to MS Word. While not strictly necessary, you may like to fully qualify the objects like Dim oTable as Word.Table, oRow as Word.Row. This is called early-binding.
2. Alternatively, to use late-binding method, you must declare the objects as generic Object type: Dim oTable as Object, oRow as Object. With this method, you do not need to add the reference to Word, but you also lose the intellisense assistance in the VBE.

I have not tested your code but I suspect ActiveDocument won't work in Excel with method #2, unless you properly scope it to an instance of a Word.Application object. I don't see that anywhere in the code you have provided. An example would be like:

Sub DeleteEmptyRows()
Dim wdApp as Object
Dim oTable As Object, As Object, _
TextInRow As Boolean, i As Long
Set wdApp = GetObject(,"Word.Application")
Application.ScreenUpdating = False
For Each oTable In wdApp.ActiveDocument.Tables

I am late for the party. Try replacing as below, mine worked perfectly- "DOMDocument" to "MSXML2.DOMDocument60" "XMLHTTP" to "MSXML2.XMLHTTP60"

Sub DeleteEmptyRows() Worksheets("YourSheetName").Activate On Error Resume Next Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

The following code will delete all rows on a sheet(YourSheetName) where the content of Column A is blank.

EDIT: User Defined Type Not Defined is caused by "oTable As Table" and "oRow As Row". Replace Table and Row with Object to resolve the error and make it compile.