Solved: A simple word table selection query

[FhM]

I am validating data in a tables in a large document the criteria is that the macro looks for the first cell in each row with certain names then checks if cell 4-7 in that row are empty and if so delete it.
I know the solution will be simple but I cant quite seem to grasp it.

Here is what I have so far:


Sub CleanUpTables()
Dim oTbl As Table
Dim oRow As Row
Dim oRng As Range
Dim oRng2 As Range
Dim FirstCell As Cell
Dim SecondCell As Cell


Dim numColumns As Integer

For Each oTbl In ActiveDocument.Tables

numColumns = oTbl.Columns.Count

For Each oRow In oTbl.Rows
Set oRng = oRow.Cells(1).Range
oRng.End = oRng.End - 1
Set oRng2 = oRow.Cells(4).Range

oRng2.End = oRng.End - 1

'And oRow.Cells(5).Text = "" And oRow.Cells(6).Text = "" And oRow.Cells(7).Text = "" Then

Select Case oRng.Text
Case "Name1"
MsgBox oRng.Text & vbCrLf & oRng2.Text
If oRng2.Text = Empty Then
oRow.Delete
End If

Case "Name2"
MsgBox oRng.Text & vbCrLf & oRng2.Text
If oRng2.Text = Empty Then
oRow.Delete
End If

Case "Name3"
MsgBox oRng.Text & vbCrLf & oRng2.Text
If oRng2.Text = Empty Then
oRow.Delete
End If
End Select


'End With
Next

Next

End Sub
Any help greatly appreciated. Once I get this working I can adapt it for several other macros I need to write.

[FhM]

Ok I have found a construct like this for what I need but it seems a little clumsy and it would mean doing some kind of count variable as well to see if all cells were empty

Case "name3"
For j = 4 To 7
Tmp = oTbl.Cell(RowNum, j).Range
If InStr(1, Tmp, "") > 0 Then
MsgBox Tmp
End If
Next

I'm sure there must be a better way of doing this but I will run with it, if thats about the best I can do.

[FhM]

Turns out the Instr method doesn't actually work so I have changed it to this:

Case "name3"
For j = 4 To 7
Tmp = oTbl.Cell(RowNum, j).Range
'If InStr(1, Tmp, Empty) < 0 Then
If Tmp = Chr(13) & Chr(7) Then
'MsgBox Tmp
MsgBox "no Data in cell " & RowNum & " " & j
Else: MsgBox "Data found in cell* " & RowNum & " " & j
End If
Next

[fumei]

"Turns out the Instr method doesn't actually work "

What do you mean? InStr works. However, from a string POV, an "empty" cell is NEVER - ever - "empty". All Word table cells have an end-of-cell marker, and it IS considered a length of string.

Say table cell(2,2) is "empty".
MsgBox Len(ActiveDocument.Tables(1).Cell(2, 2).Range.Text)
will return "2". The length of the "empty" cell range.text is 2. Chr(13) and Chr(7). I can see that you know this because you are using these.

If you are doing any sort of regular text manipulation of Word table, it is a good idea to have a generic function that returns the "actual" text. Like this:
Function CellText(strIn As String) As String
CellText = Left(strIn, Len(strIn) - 2)
End Function
Keep that function in a global container (either Normal, or a global). Now, whenever you need to get just the text from a table cell, use the function.

MsgBox CellText(ActiveDocument.Tables(1).Cell(1, 2).Range.Text)
This returns only the text part of the cell. This is better than constantly having to do range manipulation for any given procedure like:
oRng2.End = oRng.End - 1
Having a working function means you can call it anytime.

Notice that when you use range manipulation, like above, you move the .End by -1, but the function uses string manipulation with a -2.

Why? Because the end-of-cell marker is unique. It is a strange amalagation of two ASCII characters, that under some scenarios (using Range) is consider ONE character, but under other scenarios (using strings) is considered TWO.

"the macro looks for the first cell in each row with certain names then checks if cell 4-7 in that row are empty and if so delete it."

That seems to be wanting to check cell 4 TO 7. Your code only checks Cell(4).

You declare numColumns, and give it a value...and then never use it. Why?
numColumns = oTbl.Columns.Count

As your Select case does the same thing for all your cases, you may as well put them together. I am not sure if I fully understand the logic, but:
Sub CleanUpTables()
Dim oTbl As Table
Dim r As Row

For Each oTbl In ActiveDocument.Tables
For Each oRow In oTbl.Rows
Select Case CellText(oRow.Cells(1))
Case "Name1", "Name2", "Name3"
If CellText(oRow.Cells(4).Range.Text) Or _
CellText(oRow, Cells(5).Range.Text) Or _
CellText(oRow.Cells(6).Range.Text) Or _
CellText(oRow.Cells(7).Range.Text) _
= "" Then
oRow.Delete
End If
End Select
Next
Next
End Sub
means that for each row, in each table, if the text of cell(1) of the row is "Name1", or "Name2", or "Name3", and any of the cells 4 - 7 are "" - "empty" - then the entire row is deleted.

[FhM]

Thank you for your reply. I will look into it in greater depth later. The NumColumns is from another piece of code I have. This one was just altered from that so there maybe some "leftovers". Thanks again.

[FhM]

Many thanks I have this implemented and working now. I needed to know if multiple cells were empty rather than just one as one can be an oversight and all of those cells means it needs to be deleted. Anyway I ended up with a construct like this as I couldn't think of a more elegant way with all the and bits.

Sub CleanUpTables()
Dim oTbl As Table
Dim r As Row

For Each oTbl In ActiveDocument.Tables
For Each oRow In oTbl.Rows
Select Case CellText(oRow.Cells(1))
Case "Name1", "Name2", "Name3"
If CellText(oRow.Cells(4).Range.Text) = "" and _
CellText(oRow, Cells(5).Range.Text) = "" and _

CellText(oRow.Cells(6).Range.Text) = "" and _

CellText(oRow.Cells(7).Range.Text) = "" _
Then oRow.Delete
End If
End Select
Next
Next
End Sub