Macro for MS Word

[ubns]

I was wondering if someone can provide macro for ms word. I want to do the following:

if there is a row such as:



Cheryl 0 1 0 0 1
Chile 0 0 0 0 0

Word should delete the row with all zeros (except in the first column of that row).

So Row Chile should be deleted completely.

[macropod]

Hi umeshbanga,

Are these 'rows' in a Word table? If so, how many tables are there in the document? If there is more than one table, which tables do you want processed?

[ubns]

Hi Paul,

Yes these rows are in Word Table.

There are around 5-20 tables in MS Word. every time the number of tables can change, but the maximum table could go upto 20.

I want to process all tables.

Regards

Umesh Banga

[macropod]

Your requirements are ambiguous:

Cheryl 0 1 0 0 1
Chile 0 0 0 0 0

Word should delete the row with all zeros (except in the first column of that row).
So Row Chile should be deleted completely.

So, do you want to end up with:
Cheryl 0 1 0 0 1
Chile
or:
Cheryl 0 1 0 0 1

[ubns]

apologies, If I didnot make myself clear enough.

I should end up with Cheryl 0 1 0 0 1 row only.

Regards

[macropod]

Try:

Sub CleanTables()
Application.ScreenUpdating = False
Dim oTbl As Table, Rng As Range, i As Long, j As Long, StrTmp As String
With ActiveDocument
  For Each oTbl In .Tables
    With oTbl
      j = .Columns.Count
      For i = .Rows.Count To 1 Step -1
        With .Rows(i)
          Set Rng = .Range
          Rng.Start = .Cells(2).Range.Start
          StrTmp = Replace(Replace(Replace(Replace(Rng.Text, Chr(7), ""), vbCr, ""), " ", ""), "0", "")
          If StrTmp = "" Then .Delete
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

[ubns]

Hey mate,

it does not work, it says :

Rng.Start = .Cells(2).Range.Start ----------

on this row it says
Run time error 5941, the requested member of the collection does not exist.

[macropod]

Hi umeshbanga,

I coded the macro in the expectation that your names & numbers were in different cells on the same row. The error you're getting suggests there's only one cell per row. Is that correct?

[ubns]

no. They all are in different cells. So it is table with 6 rows and 5 columns (means row is divided in 5 cells).

[macropod]

The error, though, suggests the macro is encountering a row with only one cell.

[ubns]

can there be any other reason.

[ubns]

Its works man.

I know what happened. I have got a document, in which there are heaps of tables. There are tables ranging from ------- one row and only one column (only one cell in a row), and to 20 rows and 20 columns.

The moment this code find the table with one cell only, its stops working and throughs an error.

Do you think there is a way around that, I mean - in case if there is a table with one cell only, then the code just ignores it and look for another table, and keeps doing it untill it reaches the end of the document.

[ubns]

BTW thanks heaps, you are genious.

[macropod]

Try:

Sub CleanTables()
Application.ScreenUpdating = False
Dim oTbl As Table, Rng As Range, i As Long, j As Long, StrTmp As String
With ActiveDocument
  For Each oTbl In .Tables
    With oTbl
      j = .Columns.Count
      For i = .Rows.Count To 1 Step -1
        With .Rows(i)
          If .Cells.Count > 1 Then
            Set Rng = .Range
            Rng.Start = .Cells(2).Range.Start
            StrTmp = Replace(Replace(Replace(Replace(Rng.Text, Chr(7), ""), vbCr, ""), " ", ""), "0", "")
            If StrTmp = "" Then .Delete
          End If
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

[ubns]

Hi Paul,

Thanks for this extensive code.

When I run this code: its gives the run time error - 5991 - cannot access individual rows in this collection because the table has vertically merged cells.

PS - When I click on debug --- it stop to execute code (as expected) and highlights the sentence in the code (as expected again), indicating the possible source of error.

However, when I click on End - it delete the rows with zero.

So with my limited knowledge may be there is a need to include exception handling or something. Anyways I am just guessing.

Mate also I wanted to ask you, which is the best source for beginners to learn all this. You seem to have so much knowledge, its awesome and quite amazing.

Do you also have forums for excel?