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
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:
Dim oTable as Word.Table, oRow as Word.Row
. This is called early-binding. 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.
Source: Stackoverflow.com