[Writer] Macro to delete empty rows in tables

[JackoxD]

Hi,
I’ve made a file that contains links to the calc file. Once opened, the links update.
There are 7 tables in the writer file, each containing 100 rows.
If the data in the calc file is less than 100, the remaining rows are blank after the update.
I need a macro that will remove empty rows from each table. Could be on button.
I never wrote macros. I do not know this language. Can I count on your help?
I can delete rows from each table myself. But the file is intended for use by my dad, and it is difficult for him.
optionally, it would be great if someone had a macro to remove links from external files. Because before you delete the rows it is necessary.

EDIT:
I found samething:

Sub removeEmptyRows)
    oTables = ThisComponent.TextTables
    For i = 0 To oTables.getCount() - 1
       nr = oTables(i).getRows().Count
       for n = nr to 1 step -1
         if  oTables(i).getCellByName(A & n).getString() = "" then oTables(i).getRows().RemoveByIndex(n-1,1)
       next
    Next
End Sub

Everything works, but it works always when column A is empty. What I want is delete when columns A and B are empty (or better - when all columns in row are empty)

[JohnSUN]

Not sure that this code will work with DDE-tables - if you turn linked tables into regular text tables (Edit - Links-Break Link) before running the macro, then this will certainly work fine. And according to the description of your task, can we break the link with the spreadsheet?

[JohnSUN]

Unfortunately .RemoveByIndex() method will not work with rows of the DDE-link table.

Moreover, you cannot delete rows from this table even manually.

If the rows don’t have to be deleted, but can only hide them (make them of zero height), then the macro may look like this:

Sub HideAllEmptyRows
Dim oTextTables As Variant
Dim oTable As Variant
Dim oRows As Variant
Dim oData As Variant
Dim i As Long,  j As Long
	oTextTables = ThisComponent.getTextTables()
	For j = 0 To oTextTables.getCount() - 1
		oTable = oTextTables.getByIndex(j)
		oRows = oTable.getRows()
		oData = oTable.getDataArray()
		For i = UBound(oData) To LBound(oData) Step -1
			If Trim(Join(oData(i),"")) = "" Then 
				oRows.getByIndex(i).SizeType = 1
			Else 
				oRows.getByIndex(i).SizeType = 0
			EndIf 
		Next i
	Next j
End Sub

By the way, this same technique - If Trim(Join(oData(i),"")) = "" Then check - can be used in your macro if you can break the connection with data.

[JackoxD]

Okey, I did it… but not all yet…
Example below works for every tables:

Sub removeEmptyRows)
    oTables = ThisComponent.TextTables
    For i = 0 To oTables.Count - 1
       nr = oTables(i).getRows().Count
       for n = nr to 1 step -1
         if  oTables(i).getCellByName(A & n).getString() = "" then oTables(i).getRows().RemoveByIndex(n-1,1)
       next
    Next
End Sub

Example below works for specyfic table (you can change a .getCellByName(A…) to different letter (column):

Sub removeEmptyRows)
    oTables = ThisComponent.TextTables.getByName("Table2")
       nr = oTables.getRows().Count
       for n = nr to 1 step -1
         if  oTables.getCellByName(A & n).getString() = "" then oTables.getRows().RemoveByIndex(n-1,1)
       next
    Next
End Sub

But I have one table where every second row is empty in first column:

       A                 B      C      D
1     1          (data)
2   (blank)      (data)
3     2          (data)
4   (blank)      (data)
5     3          (data)
6   (blank)
7   (blank)
8   (blank

What i want is merge cells A1 and A2, A3 and A4, A5 and A6 etc… Is possibile?

[JohnSUN]

Once more - you can not merged cells of linked table (“The structure of a linked table cannot be modified.”).

If the connection to the data source is broken before formatting the tables, then there will be no problems with any modification of the tables.

If this condition is acceptable, then we can try to solve this variant of the task.