Delete values in last cell of rows based on values in other cells

Posted on 2013-10-17
Medium Priority
Last Modified: 2013-10-21
Dear Experts:

My current document has hundreds of tables. I would like to perform the following task using VBA.

As soon as the macro hits the value '99-99' (The value '99-99' is always located in the first cell of any row) in any of the rows, the contents of the last cell in that row is to be deleted.

All of the tables in this document feature the exact same number of columns.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Question by:Andreas Hermle
LVL 37

Expert Comment

ID: 39582430
Possibility for a sample document?
LVL 59

Accepted Solution

Chris Bottomley earned 2000 total points
ID: 39582431

I was concerned to avoid iterating on row as this would be inefficient so please try the following on a copy of your data:

Sub Q_28270411_1()
Dim tbl As Table
Dim cols As Integer
Dim intCol As Integer
Dim rng As Range
Dim subRange As Range

    Set rng = ActiveDocument.Range
    With rng.Find
        .Text = "99-99"
        Do While .Execute(Forward:=True) = True
            If rng.Information(wdWithInTable) Then
                Set subRange = ActiveDocument.Range(rng.Start, rng.End)
                subRange.Collapse Direction:=wdCollapseEnd
                If subRange.Cells(1).ColumnIndex = 1 Then
'                    Debug.Print rng.Tables(1).Rows(rng.Cells(1).RowIndex).Cells(rng.Tables(1).Columns.Count).Range.Text
                    rng.Tables(1).Rows(rng.Cells(1).RowIndex).Cells(rng.Tables(1).Columns.Count).Range.Text = ""
                End If
            End If
    End With

End Sub

Open in new window


Author Closing Comment

by:Andreas Hermle
ID: 39587301
Hi Chis,

great job. Works like a charm. Saves me lots of time. Thank you very much for your swift and professional support.

Regards, Andreas

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question