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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

[excel] Excel VBA Macro: User Defined Type Not Defined

Examples related to excel

Examples related to vba

Examples related to ms-word