Solved: Range with table cells

[TrippyTom]

I want to get a better grasp on utilizing Range in the most efficient way. My goal is to simply remove ALL spaces in all cells except the cells in column 1. I was wondering if there was a different way to assign the Range to make the following code faster:
Sub removeSpaces()
Dim myRow As Long
Dim myCol As Long
Dim myCell As Cell
Dim r As Range
Application.ScreenUpdating = False

' Do on all cells except COL1
For myRow = 1 To ActiveDocument.Tables(1).Rows.Count
For myCol = 2 To ActiveDocument.Tables(1).Columns.Count
Set r = ActiveDocument.Tables(1).Cell(myRow, myCol).Range
With r
.Find.ClearFormatting
.Find.Replacement.ClearFormatting
With r.Find
.Text = " "
.Replacement.Text = ""
.Forward = True
'.Wrap = wdFindAsk
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchByte = False
.CorrectHangulEndings = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = False
.MatchFuzzy = False
End With
r.Find.Execute Replace:=wdReplaceAll
End With
Next myCol
Next myRow

Application.ScreenUpdating = True
End Sub

[fumei]

You do not have to assign a range at all. Think about what you want to do.

"My goal is to simply remove ALL spaces in all cells except the cells in column 1."

What is the logic?

IF a cell is NOT in Column1, THEN remove any " " from that cell.

So, the logic is:

1. test if the cell is in column 1
2. if it is NOT, replace any " ", with ""


Dim oCell As Cell
For Each oCell In ActiveDocument.Tables(1).Range.Cells
If oCell.ColumnIndex <> 1 Then ' logic step #1
oCell.Range.Text = _
Replace(oCell.Range.Text, " ", "") ' logic step #2
End If
Next
Done. However....

If you test the above, you will notice that there could be a change. A paragraph mark that did not seem to be there before. This has to do with the existence of the end-of-cell marker in each cell. If this is an issue (and most likely it would be), then you have to deal with that end-of-cell marker. Here is the same code, with the added Function to deal with the end-of-cell marker.

If you are dealing with text from table cells often, I would recommended that you have the Function globally available, and use it.

Option Explicit

Function CellText(strIn As String) As String
CellText = Left(strIn, Len(strIn) - 2)
End Function

Sub yaddaSpace()
Dim oCell As Cell
For Each oCell In ActiveDocument.Tables(1).Range.Cells
If oCell.ColumnIndex <> 1 Then
oCell.Range.Text = _
Replace(CellText(oCell.Range.Text), " ", "")
End If
Next
End Sub

[fumei]

Just to expand things...

Tables(1).Cell has to have a row and column value, that is:

Tables(1).Cell(2,2)

Tables(1).Range.Cells - notice the plural! - does NOT have a row and column value, as it is, in fact, the Collection of cells within the range.

Tables(1).Cell is a pointer to a specific cell, thus it must have explicit values, i.e. a row and column value.

Tables(1).Range.Cells - being a Collection - has an index number:

Tables(1).Range.Cells(6)

The sixth cell in the table regardless of what row or column it is in.

This can be very significant when trying to access merged cells in a table. Merged cells are the bain of VBA coding, as they can cause VBA - if you are using row/column values! - to choke and die.

As an aside, I hate merged cells in tables. They are a real pain in the butt.

[fumei]

Lastly, I know it was probably inadvertently done, but try to avoid declaring objects and never using them.

You declare myCell As Cell, but you never actually Set it, or use it.

[fumei]

Please please please TrippyTom, do not take this as any sort of personal criticism. I do not intend it that way. I am, really, trying to help.

This is precisely what I was trying to talk about in my Logic vs Code thread.

As you can see my code is much simpler than yours. The reason for that is because my code deals with the actual logic requirements.

The first logic requirement was that the cell must NOT be in column 1.

So, from a question point of view, the question would be:

"How do I action all cells that are NOT in column 1?"

The answer would be: "Check to see if it IS in column 1. That is done by testing the value of ColumnIndex."

Are you following this?

Your code logic is this:

1. action each row with a counter
2. with each row, action each column with a counter starting at the second column
3. set a range object for each cell
4. use Range.Find to do a ReplaceAll

To give you an idea of what actually happens, lets put a counter to count how many loops REALLY happen within your code.
Dim j As Long

For myRow = 1 To ActiveDocument.Tables(1).Rows.Count
j = j + 1
For myCol = 2 To ActiveDocument.Tables(1) _
.Columns.Count
j = j + 1

This gets a counter of how many times VBA has to loop back to the beginning of your looping - the For myRow =, and the For myCol =.

For a table with 4 rows, 4 columns (16 cells), the action will process the string manipulation on 12 of them...yes?

How many loops are there?

Answer: 16

My code:
For Each oCell In ActiveDocument.Tables(1).Range.Cells
If oCell.ColumnIndex <> 1 Then ' logic step #1
oCell.Range.Text = _
Replace(oCell.Range.Text, " ", "") ' logic step #2
End If
Next has no loops and no counters. It does ONLY what is logically required.

If a cell is NOT in column 1, do something.

[TrippyTom]

I'm not offended at all. In fact, that's why I posted the question - to learn. I'm always looking for ways to make more efficient code and learn at the same time. That's the purpose of this forum.

I figured there had to be a better way without using loops, but I didn't know how to approach it. Thus, my planning wasn't as detailed as I thought it was.

In this case, I think the big difference was the distinction between cell and cells, as you pointed out. I guess I had RANGE on the brain because of your other threads in the Word forum. :) I just figured I had to use RANGE everywhere.

[fumei]

chuckle

Basically, you are correct. Generally speaking it IS better to use Range everywhere.

If you look, I did use range in my code. I just did not use, or set, a range object. I used the range of the table in order to access the Cells (plural!) collection of the range - which does not require row/column values.

As opposed to the Cell (singular!) collection of the table - which DOES require row/column values.