Excel VBA Macro: User Defined Type Not Defined

Question

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 appears to be code for Word, but you've tagged it as Excel. Where are you trying to run it? — Rory
– Rory, Commented Jun 17, 2014 at 10:52
Yes my apologies, the code is for a mail merge in Word, executed in Excel — holdo1
– holdo1, Commented Jun 17, 2014 at 11:01

David Zemens · Accepted Answer · 2014-06-17 12:09:00Z

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:

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.
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

GideonMetre · Accepted Answer · 2019-05-02 15:38:54Z

0

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

answered May 2, 2019 at 15:38

GideonMetre

11 bronze badge

Comments

Olle89 · Accepted Answer · 2014-06-17 10:51:03Z

-1

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.

answered Jun 17, 2014 at 10:51

Olle89

6787 silver badges22 bronze badges

1 Comment

user20416 Over a year ago

It may compile, but will probably not run, without the relevant Word objects, either via early binding or late binding, as in this answer.

Collectives™ on Stack Overflow

Excel VBA Macro: User Defined Type Not Defined

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related