Sure. I hesitate because I don't want to dig you in too deep, especially
with code that "deletes things"
You WILL test this in a COPY, right?
The simple answer to your question is "Sure, look only in cell 2!". But to
do that, we have to restructure the original code so we return each row as a
"Row" object (which is a collection of cells) rather than as a "Range"
object, which is a span of text. Once we have each row as a collection of
cells, it's trivial to say we want to examine the contents of "Cell number
2".
The original code is presumably designed to do something else. It's not the
most efficient way of doing THIS task. Efficiency doesn't matter much in
VBA, because such routines normally do not run long enough for the lack of
it to be measurable. But not having any more code to maintain than you NEED
is goodness. As part of that, making the COMPILER do the work is greatness.
In that original code, there were several instances where the author is
creating things that already exist in the VBA environment. Under some
circumstances, there are reasons why you have to do things that way, and I
don't know what the original author's circumstances are. But for the simple
problem you are presenting here, the following is less code and easier to
read:
Sub DeleteRow()
Dim oTable As Table, oRow As Row, oCell As Cell, _
TextInRow As Integer
' Specify which table you want to work on.
Set oTable = Selection.Tables(1)
For Each oRow In oTable.Rows
StatusBar = "Row " & oRow.Index
If Not oRow.HeadingFormat Then
If Len(oRow.Cells(2).Range.Text) < 3 Then
'end of cell marker is actually 2 characters
oRow.Select
TextInRow = MsgBox("Delete this row", vbOKCancel)
If TextInRow = 1 Then oRow.Delete
End If
End If
Next ' oRow
End Sub
The above code stops at each row and asks. If you don't want it to do that,
then change it this way:
Sub DeleteRow()
Dim oTable As Table, oRow As Row, oCell As Cell
' Specify which table you want to work on.
Set oTable = Selection.Tables(1)
For Each oRow In oTable.Rows
StatusBar = "Row " & oRow.Index
If Not oRow.HeadingFormat Then
If Len(oRow.Cells(2).Range.Text) < 3 Then oRow.Delete
'end of cell marker is actually 2 characters
End If
Next ' oRow
End Sub
Notes:
1) You do not have to set a counter for most collections. The system
already maintains one. In this case, it's ".Index".
2) If all you want to do is iterate an entire collection, use a "For
Each..." loop. The Visual Basic Environment then declares the necessary
variable and constructs the control loop for you.
3) If you want to read the Status Bar, don't turn Screen Updating off
4) For most tables, you don't want to delete the heading rows, whether they
have text in them or not. So test for the "Heading Rows repeat" property
(named "HeadingFormat" in VBA, and skip the row if it has that.
5) By turning your test inside out (< 3 rather than > 2) you can save
yourself a branch and a lot of lines of code. So instead of storing the
state in the variable, we can simply hammer the row if cell 2 is blank.
Now, to see if you have been paying attention, "Why did the original author
do it the other way?"
6) This macro will run slowly anyway. Anything to do with tables crawls in
Word 2004. But you can make it "slightly" faster by adding a comment
character in front of the name of the control variable at the end of the For
loop. That turns the name of the loop into "text" which does not have to be
evaluated, rather than a "variable" which does have to be evaluated. You
will get a completely undetectable improvement in speed doing that, but you
will satisfy my perfectionist sickness
Cheers
Ok...so do you have any suggestions as to how i can change the code for it
only to look in the second column of the table?
Cheers
John McGhie [MVP - Word and Word Macinto said:
The code you posted looks in "each" cell in each entire row.
The construct " For Each oCell In oRow.Rows(1).Cells" expands to "For each
of the cells in the entire row within the nominated range..."
I would have coded the variable as "rRow" rather than "oRow" because it is a
range, not an object. To my mind, that makes the code difficult for humans
to understand.
Cheers
I have seen the following code on the MVPS website, but this only looks at
the cells in the first column of the table, i need it to look at the second
column of the table. Is this possible?
****Public Sub DeleteEmptyRows()
Dim oTable As Table, oRow As Range, oCell As Cell, Counter As Long, _
NumRows As Long, TextInRow As Boolean
' Specify which table you want to work on.
Set oTable = Selection.Tables(1)
' Set a range variable to the first row's range
Set oRow = oTable.Rows(1).Range
NumRows = oTable.Rows.Count
Application.ScreenUpdating = False
For Counter = 1 To NumRows
StatusBar = "Row " & Counter
TextInRow = False
For Each oCell In oRow.Rows(1).Cells
If Len(oCell.Range.Text) > 2 Then
'end of cell marker is actually 2 characters
TextInRow = True
Exit For
End If
Next oCell
If TextInRow Then
Set oRow = oRow.Next(wdRow)
Else
oRow.Rows(1).Delete
End If
Next Counter
Application.ScreenUpdating = True
End Sub
Note that you could delete the empty rows from all tables in a document by
replacing the line:
Set oTable = Selection.Tables(1)
With the line
For Each oTable In ActiveDocument.Tables
and adding the line:
Next oTable
just before:
Application.ScreenUpdating = True*****
Thankyou.
--
Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.
John McGhie <
[email protected]>
Microsoft MVP, Word and Word for Macintosh. Business Analyst, Consultant
Technical Writer.
Sydney, Australia +61 (0) 4 1209 1410
--
Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.
John McGhie <
[email protected]>
Microsoft MVP, Word and Word for Macintosh. Business Analyst, Consultant
Technical Writer.
Sydney, Australia +61 (0) 4 1209 1410
