[excel] 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

This question is related to excel vba ms-word

The answer is


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. enter image description here
  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.


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to ms-word

continuous page numbering through section breaks How do I render a Word document (.doc, .docx) in the browser using JavaScript? Excel VBA Macro: User Defined Type Not Defined How can I change text color via keyboard shortcut in MS word 2010 Getting char from string at specified index Create auto-numbering on images/figures in MS Word Convert Word doc, docx and Excel xls, xlsx to PDF with PHP Return multiple values from a function, sub or type? What is a correct MIME type for .docx, .pptx, etc.? What is the best way to insert source code examples into a Microsoft Word document?