Delete row with empty cel in a table

vibor · #1 04-26-2015, 05:19 AM

Hello,

A process is filling a words document (MS Words 2007).

A table in this document has 60 row's. Depending on the process there are 1 or all 60 rows filled.
Every cell has a bookmark. (3 cells in a row, number, problem, solution)
I know how to find the cells and how to select and delete the row.
I want delete the empty rows starting at the end.
When there is data/content, than stop macro.

My problem is:
1 How do I check the cell if its empty?
2 Do I have to repeat the routine 60 times, or is there a other way?
This is what I have:

Sub DeleteLegeRegel()
Selection.GoTo What:=wdGoToBookmark, Name:="Prob60"

(CHECK CEL FOR DATA, HOW?)

If empty Then
Selection.Expand (wdRow)
Selection.Cut
Selection.GoTo What:=wdGoToBookmark, Name:="Prob59"
(CHECK CEL FOR DATA, HOW?)

If empty Then
Selection.Expand (wdRow)
Selection.Cut
Selection.GoTo What:=wdGoToBookmark, Name:="Prob58"

Else
Selection.GoTo What:=wdGoToBookmark, Name:="Prob59"

End If

End Sub

It shout be simple, but I can't find a proper example.

Grtz Vincent

Guessed · #2 04-26-2015, 04:39 PM

You haven't made it clear what constitutes empty but if we assume that the bookmark length = 0 means empty then you might do it this way

Code:

Sub Cleanup()
  Dim x As Integer, sBkmk As String
  For x = 60 To 1 Step -1
    sBkmk = "Prob" & x
    If ActiveDocument.Bookmarks.Exists(sBkmk) Then
      If Len(ActiveDocument.Bookmarks(sBkmk).Range.Text) = 0 Then
        ActiveDocument.Bookmarks(sBkmk).Range.Rows(1).Delete
      End If
    End If
  Next x
End Sub

gmaxey · #3 04-27-2015, 06:15 AM

Here is another way. An empty cell has a text length = 2. A row marker has a text length = 2. So an empty row with three cells has a text length = 8.

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
  Do
    If Len(Selection.Tables(1).Rows.Last.Range.Text) = 8 Then
      Selection.Tables(1).Rows.Last.Delete
    Else
      Exit Do
    End If
  Loop
End Sub

vibor · #4 04-27-2015, 02:42 PM

Thank you Guessed and gmaxey,

The solution from gmaxey is working (

Whoow!).
Great great! Thanks.

I Have 2 tables, 1 with 4 and one with 3 coulombs.
So the code is:

HTML Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
 
'check table 11
Selection.GoTo What:=wdGoToBookmark, Name:="Nr11"
Do
If Len(Selection.Tables(1).Rows.Last.Range.Text) = 10 Then
Selection.Tables(1).Rows.Last.Delete
Else
Exit Do
End If
Loop
'Check table 15
Selection.GoTo What:=wdGoToBookmark, Name:="Nr15"
Do
If Len(Selection.Tables(1).Rows.Last.Range.Text) = 8 Then
Selection.Tables(1).Rows.Last.Delete
Else
Exit Do
End If
Loop
 
End Sub

Although this is working OK, I have still 2 problems.
1st. is the make up/cosmetic:
The inner horizontal lines of the table are gray, the borders and the vertical lines are black.
After the scratch the bottom line is grey. What can I do about it?

2nd.
There is another table in my document with 11 coulombs.
In coulomb 1 and 7 is always a number present.
How can I ignore coulombs 1 and 7?

I hope you can help me. It would be very nice.

GrtZ ViBoR

gmaxey · #5 04-27-2015, 06:48 PM

This should address your two follow on questions:

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngIndex As Long, lngLength As Long
Dim oRow As Row
  If Selection.Tables(1).Columns.Count = 3 Then
    Do
      If Len(Selection.Tables(1).Rows.Last.Range.Text) = 8 Then
        Selection.Tables(1).Rows.Last.Delete
      Else
        Exit Do
      End If
    Loop
    Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color
  End If
  If Selection.Tables(1).Columns.Count = 7 Then
    Do
      Set oRow = Selection.Tables(1).Rows.Last
      lngLength = 0
      For lngIndex = 2 To 6
        lngLength = lngLength + Len(oRow.Cells(lngIndex).Range.Text)
      Next lngIndex
      If lngLength = 10 Then
        oRow.Delete
      Else
        Exit Do
      End If
    Loop
  End If
  Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color
End Sub

vibor · #6 04-28-2015, 03:28 AM

Hallo Greg,

Thank you for replay.

Its getting better but ...some little things:

The process is specific for 3 tables. There are many tables in the document, and I don't want to check al the tables. So I changed it a bit and use the bookmarks to find the table.

Table 11 and 15 are working OK.

But table 9 not.
Only the last row will be deleted even if there is data in the row.

The row has 11 coulombs, in coulomb 1 and 7 there's data to ignore.

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngIndex As Long, lngLength As Long
Dim oRow As Row
 
'check table 9
Selection.GoTo What:=wdGoToBookmark, Name:="RM1a"
    Do
      Set oRow = Selection.Tables(1).Rows.Last
      lngLength = 0
      For lngIndex = 2 To 5
        lngLength = lngLength + Len(oRow.Cells(lngIndex).Range.Text)
      Next lngIndex
      Selection.Tables(1).Rows.Last.Delete
      Exit Do
      Loop
Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color
'check table 11
  Selection.GoTo What:=wdGoToBookmark, Name:="ItemNr0"
  Do
    If Len(Selection.Tables(1).Rows.Last.Range.Text) = 10 Then
      Selection.Tables(1).Rows.Last.Delete
    Else
      Exit Do
    End If
  Loop
Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color
 
'Check table 15
  Selection.GoTo What:=wdGoToBookmark, Name:="OvopItem1"
  Do
    If Len(Selection.Tables(1).Rows.Last.Range.Text) = 8 Then
      Selection.Tables(1).Rows.Last.Delete
    Else
      Exit Do
    End If
  Loop
Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color
End Sub

The tables are started with a header of 2 rows
So the data starts in the 3e row.
Is it possible for table 11 and 15 to check the 3 row thirst for data in coulomb 3 and 4, if there is no data place a text in second row: 'No problems'.
So there is always one row with a message.

Table 9 has to be completely delectated when there is no data at al.

I hope you would help me again!

GrZ ViBoR
Holland

gmaxey · #7 04-28-2015, 05:07 AM

I'm going to be out the rest of the day. This should resolve the table 9 issue:

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngIndex As Long, lngLength As Long
Dim oRow As Row
 
  'check table 9
  Selection.GoTo What:=wdGoToBookmark, Name:="RM1a"
  Do
    Set oRow = Selection.Tables(1).Rows.Last
    lngLength = 0
    For lngIndex = 2 To 11
      If lngIndex <> 7 Then
        lngLength = lngLength + Len(oRow.Cells(lngIndex).Range.Text)
      End If
    Next lngIndex
    If lngLength = 18 Then
      Selection.Tables(1).Rows.Last.Delete
    Else
      Exit Do
    End If
  Loop
  Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color
End Sub

I don't know what you are after with your other question. Besides I've given you enough code that you should at least be able to try something on your own. If you can't work it out the post back.

Best Regards,
Greg Maxey

The dogs bark but the caravan moves on.
~ Persian Proverb

vibor · #8 04-30-2015, 02:41 PM

Halo Greg,

Sorry but this part (table9) is not working. I've checked your code and can't find a fault in it. I tried to change some parameters but it won't work.

Do you have an idea to fix it?

Table 11 and 15 works fine.
I've tried to make it even better to place a text in the first row with a text "No problems". After that the empty rows are deleted. But I can't get the text in the desired cell (Opmerking0).
This is watt I tried:

Code:

Sub checkEmptyFirstRow()
   Selection.GoTo What:=wdGoToBookmark, Name:="Opmerking0"
   If Len(Selection.Tables(1).Rows.Last.Range.Text) = 10 Then
   ActiveDocument.Bookmarks("Opmerking0").Range.Text="No problems"
   End If
End Sub

I think 'last' is wrong in the code, the current row must be read out and the text in the second cell. In this cell there's a bookmark called 'Opmerking0'.

Above the thirst (data) row there are 2 header rows.
Sorry if my English is poor.

Grt ViBoR
From Holland

gmaxey · #9 04-30-2015, 05:29 PM

I don't know how to fix it because I don't know what is not working. You said you have a 11 column table with data always in column 1 and 7. The code I provided you looks at columns 2-6 and 8-11 starting with the last row of the table. If they are all empty then the row is deleted.
The first time a row contains content in 2-6 or 8-11 the execution stops.

vibor · #10 05-03-2015, 05:42 AM

Hello Greg,

Sorry your code is OK.

I forgot a few coulombs with a checkbox. They are count one extra in the form. It doesn't matter or the boxes are on or off.

So table 9 is also working fine for deleting empty row's.
Thank you again!

The under line is also working in table 11 and 15.
In table 9 is it working when the whole inner table has a line. In table 9 I want in the middle a blank coulomb because I want a border between the right and left part.
(actually it are two tables next to each other). When I do this, the line
Selection.Tables(1).Rows.Last.Borders(wdBorderBott om).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop ).Color
isn't working anymore.
I tried to fix it to change some codes but ...I'll think I need some help...

The test document is in the attachment.

Greetz ViBoR.
From a rainy Holland

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to delete all empty rows from all tables	braddgood	Word VBA	15	10-02-2015 01:54 PM
Delete empty cell and shift up	Linh	Word Tables	1	09-21-2014 01:52 PM
Creating VBA Code to Delete Empty Column in Table	Faugs	Word VBA	5	08-07-2014 03:29 PM
Delete lots of empty space between paragraphs.	FieldTechnician	Word	4	10-25-2013 01:14 PM
Macro to delete rows with all empty cells	ubns	Excel Programming	2	08-14-2012 02:01 AM