Vba Selection.Table memory issues

Question

I'm working on a script to check for extra spaces at the end of each cell on a table. The script works fine but I just got a couple of word docs containing tables with 4,000 words and 9,000 words and the script crashed. Memory issue? Is there a way to load cells in memory one by one instead of loading the whole table in memory?

This is my current script:

 Dim boxie As Cell

     Dim positioN As Integer

     Selection.Tables(1).Select

     For Each boxie In Selection.Cells

         Set celContent = cel.range

         celContent.End = celContent.End - 1

         positioN = celContent.End

         If Right(celContent.Text, 1) = " " Then

             celContent.Select

             celContent.HighlightColorIndex = wdDarkRed

             counter = counter + 1

         End If

     Next boxie

Much appreciated!

Answer 1

Tables with the AllowAutoFit setting active when being processed by a macro impose a significant performance hit. Allowing the display to update while the table resizes exacerbates this. Working with selections (which is what your code does with each cell) is also quite inefficient. Try something along the lines of:

Sub Demo()

Application.ScreenUpdating = False

Dim Tbl As Table, TblCll As Cell, bFit As Boolean

For Each Tbl In ActiveDocument.Tables

  With Tbl

    bFit = .AllowAutoFit

    .AllowAutoFit = False

    For Each TblCll In .Range.Cells

      With TblCll.Range

        If .Characters.Last.Previous = " " Then

          .HighlightColorIndex = wdDarkRed

        End If

        While .Characters.Last.Previous = " "

          .Characters.Last.Previous = vbNullString

        Wend

      End With

    Next

    .AllowAutoFit = bFit

  End With

Next

Application.ScreenUpdating = True

End Sub

Note that the above also has a  While ... Wend loop to delete the trailing spaces. You can delete/comment-out that or the highlight code.

As coded, the macro processes all tables in the document. If you only want to process one or more selected tables, change ActiveDocument to Selection.

Answer 2

This is great! Thanks so much, Paul.