Cycle through each row of a table?

[clhare]

Can anyone tell me how I would cycle through each row of a table if the macro is also adding rows here and there as it goes through the code (in which case the total number of rows keeps changing)?

Any help is greatly appreciated. Thanks!

[clhare]

I've gotten the macro to do exactly what I want on the row the cursor is in (which is to end up with only one column of text per row, moving additional columns of text to new rows). My file is attached.

I need to update the code so that the macro will do this for each row of the table, not just the row the cursor is in. The macro is called "TabCleanup".

Can anyone help me update this macro so that it runs through all the table rows automatically? I've been trying everything I can think of, but nothing works.

Thanks!

[mdmackillop]

Hi Cheryl
I would treat this in a similasr way to deleting rows in Excel.
Start with the last row
For each filled cell greater than 1, add a row below, transfer the text
Move up one row and repeat.

[fumei]

Cheryl, Malcolm is correct, and the key is:

Start with the last row

OK....aaackkkkk! I just looked at the file. Ummm.

There are some instances where If statements are totally valid. However, in many cases there is a better way. In your code you have a bunch of:

If lngCol = 1, and

If lngCol = 3, and

If lngCol = 5, etc.

These are all separate tests on the value of lngCol. So test on the value of lngCol! Use Select Case.


If Len(myRange.Text) > 0 Then
' Get column number of cell with text in it
lngCol = oCell.ColumnIndex
' Assign values to variables based on which columns have text
' This will tell me which is the 1st col with text, 2nd col with text, etc.
Select Case lngCol
Case 1 ' instead of If lngCol = 1
lngNbrOfTabs = lngNbrOfTabs + 1
intFirstTabCol = 1
Case 3 ' instead of If lngCol = 3
lngNbrOfTabs = lngNbrOfTabs + 1
If intFirstTabCol > 0 Then
intSecondTabCol = 3
Else
intFirstTabCol = 3
End If
Case 5
lngNbrOfTabs = lngNbrOfTabs + 1
If intSecondTabCol > 0 Then
intThirdTabCol = 5
ElseIf intFirstTabCol > 0 Then
intSecondTabCol = 5
Else
intFirstTabCol = 5
End If
' etc. etc.
End Select
End If

See? You test against the value itself.

I have not gone through it carefully, but ummm, I suspect there is a LOT simpler way to do this.

I am trying to figure out your logic.

And of course, I am quite opposed to using Selection if it is not needed...and I think it is not here. That would also remove any issue with what row the cursor is on. NOT using Selection makes the cursor location irrelevant.

[clhare]

I'm sure there's alot of things that could be done better in this macro. It's a miracle I got this far with it!

I did make the change suggested above, but how do I change the next part of the code to work from the last row in the table and then work it's way up to the first row? I've never done macros for Excel, and I'm having trouble finding information for this part:



With Selection.Tables(1).Rows
' Move tab text based on how many columns in the row have text in them
' and which columns they are
If lngNbrOfTabs > 1 Then
' Insert a new row below current row
Selection.InsertRowsBelow 1
' Copy cell text from current row to new row
ActiveDocument.Tables(1).Rows(lngRow + 1).Cells(intSecondTabCol).Range.Text _
= ActiveDocument.Tables(1).Rows(lngRow).Cells(intSecondTabCol).Range.Text
' Delete cell text from current row
ActiveDocument.Tables(1).Rows(lngRow).Cells(intSecondTabCol).Range.Delete
End If

[mdmackillop]

Option Explicit

Sub TabCleanup()
Dim lngRow As Long
Dim lngNbrOfColumns As Long
Dim lngNbrofRows As Long
Dim i As Long, j As Long, k As Long
' Make sure cursor is inside a table
If Selection.Information(wdWithInTable) Then
' Get current row number
lngRow = Selection.Information(wdStartOfRangeRowNumber)
' Get total number of columns
lngNbrOfColumns = ActiveDocument.Tables(1).Columns.Count
lngNbrofRows = ActiveDocument.Tables(1).Rows.Count
i = 0
lngRow = lngNbrofRows
With ActiveDocument.Tables(1)
Do
' Check each cell of current row to determine if row is empty
j = -1
For k = 1 To lngNbrOfColumns
If Len(.Cell(lngRow, k).Range.Text) > 2 Then
j = j + 1
If j > 0 Then
If lngRow = lngNbrofRows Then
.Rows.Add
Else
.Cell(lngRow, k).Range.Rows.Add BeforeRow:=.Rows(lngRow + j)
End If
.Cell(lngRow, k).Range.Cut
.Cell(lngRow + j, k).Range.PasteAndFormat (wdPasteDefault)
End If
End If
Next
lngRow = lngRow - 1
Loop Until lngRow = 0
End With
End If
End Sub

[clhare]

Holy cow!! That is awesome!!!!

It works beautifully and without that much code! I will immediately set myself to studying your code to learn how you did it!

Thank you so much!!
:bow:

[fumei]

lngRow = Selection.Information(wdStartOfRangeRowNumber)
' Get total number of columns
lngNbrOfColumns = ActiveDocument.Tables(1).Columns.Count
lngNbrofRows = ActiveDocument.Tables(1).Rows.Count
i = 0
lngRow = lngNbrofRows

Would like to point out that the initial value of lngRow is set to Selection, NEVER used as such, then reset to lngNbrofRows.

Therefore, there is no need to set it to Selection.Information(wdStartOfRangeRowNumber) in the first place. It can be set to the number of rows.

Cheryl, do you notice that there is NO use of Selection? No Selection.InsertRowsBelow. Malcolm is actioning the rows directly by cell index numbers. He is working backwards, UP from the last row.

Actually, this is not quite accurate. There is still that checking to see if the Selection is in a table stuff.

Ahem.....ahem.....ahem.

May I point out that:


' Make sure cursor is inside a table

does just that? Makes sure the Selection is in a table. A.

Ahem...of what possible significance is that when the rest of the code actions....Table(1)...regardless of whether the Selection is in that table...or not.

The code does not use Selection.Tables(1). It uses ActiveDocument.Tables(1).

So...um, the Selection could be in a table...but so what? It could be in Table 3, Table 6, Table 298. It makes no difference...ActiveDocument.Table(1) will be actioned.

So either forget about testing for the Selection, OR use Selection.Tables(1)

[mdmackillop]

Hi Gerry,
For an excuse, I was going out and wanted to get the post away, so there were some extraneous bit I never really looked at.
On the plus side, I'm starting to get the hang of Range!

[mdmackillop]

Revised code with comments. This should act on the table containing the selection.

Sub TabCleanup()
Dim lngRow As Long
Dim lngNbrOfColumns As Long
Dim lngNbrofRows As Long
Dim MyRange As Range
Dim i As Long, j As Long, k As Long
' Make sure cursor is inside a table
If Selection.Information(wdWithInTable) Then
'Determine the table index
Set MyRange = ActiveDocument.Range(Start:=0, End:=Selection.End)
i = MyRange.Tables.Count
'Get total number of columns and rows
lngNbrOfColumns = ActiveDocument.Tables(i).Columns.Count
lngNbrofRows = ActiveDocument.Tables(i).Rows.Count
'Set variable for number of rows
lngRow = lngNbrofRows
'Process table containing selection
With ActiveDocument.Tables(i)
Do
j = -1 'Counter for cells with text
'Check each cell of current row to determine if not empty
For k = 1 To lngNbrOfColumns
If Len(.Cell(lngRow, k).Range.Text) > 2 Then
'If text found, increment counter for cut/paste text
j = j + 1
'Ignore first cell with text
If j > 0 Then
'Add a row at the end of the table if checking the last row
If lngRow = lngNbrofRows Then
.Rows.Add
Else
'Add a row offset according to the text cells found
.Cell(lngRow, k).Range.Rows.Add BeforeRow:=.Rows(lngRow + j)
End If
'Cut and paste cell text
.Cell(lngRow, k).Range.Cut
.Cell(lngRow + j, k).Range.PasteAndFormat (wdPasteDefault)
End If
End If
Next
'Decrement row number
lngRow = lngRow - 1
'Exit after first row
Loop Until lngRow = 0
End With
End If
End Sub

[fumei]

Ummm, Malcolm....

Why do you bother determining the table index number? You are creating a Long variable, AND a Range object just for that purpose.



If Selection.Information(wdWithInTable) Then
'Determine the table index
Set MyRange = ActiveDocument.Range(Start:=0, End:=Selection.End)
i = MyRange.Tables.Count
'Get total number of columns and rows
lngNbrOfColumns = ActiveDocument.Tables(i).Columns.Count
lngNbrofRows = ActiveDocument.Tables(i).Rows.Count
'Set variable for number of rows
lngRow = lngNbrofRows
'Process table containing selection
With ActiveDocument.Tables(i)


If the idea is to action the table the Selection is in - after checking that it IS in a table - then just use THAT table.



If Selection.Information(wdWithInTable) Then
'Get total number of columns and rows
lngNbrOfColumns = Selection.Tables(1).Columns.Count
lngNbrofRows = Selection.Tables(1).Rows.Count
'Set variable for number of rows
lngRow = lngNbrofRows
'Process table
With Selection.Tables(1)
Otherwise, I fail to see the logic of determining if the Selection is in a table.

Yes, you can determine the table index, but...who cares since you are going to action the table the selection is (regardless of the index number), any way?

In terms of directing the action to the correct table, the index number is totally irrelevant. You are actioning the table the selection is in. End of story.

To be careful though - it may be a safe idea to collapse the Selection.

The use of Range is correct, but in this case not needed. The purpose is to action the table the Selection is in - not any table, but specifically the one the Selection is in. As you know, I work to avoid overuse of Selection, but in this case Selection IS the point.

IMO, overuse of Selection means using Selection to to enter text, or to get text. There is no harm in using the Selection object to to determine location and/or child objects. There is no harm in using Selection to set objects either.



Dim ThisTable As Word.Table
If Selection.Information(wdWithInTable) Then
Set ThisTable = Selection.Tables(1)
lngNbrOfColumns = ThisTable.Columns.Count
lngNbrofRows = ThisTable.Rows.Count
'Set variable for number of rows
lngRow = lngNbrofRows
'Process table
With ThisTable

[mdmackillop]

Keep cracking the whip Gerry!
You're correct on all points. I think my approach, excluding the initial index determination, allows for processing multiple tables (although that was not my intention admittedly)

[fumei]

Multiple tables eh? Yes..well, sure...but...um....would that not conflict with the initial logic of determining if the Selection is in a table?

Situation A: multiple tables in a Selection.

Dim oTable As Word.Table
If Selection.Tables.Count = 0 Then
Msgbox "No tables in Selection."
Exit Sub
Else
For Each oTable In Selection.Tables()
lngNbrOfColumns = oTable.Columns.Count
lngNbrofRows = oTable.Rows.Count
'Set variable for number of rows
lngRow = lngNbrofRows
'Process table
With oTable
' etc etc
End With
Next
End If
No use of Range or table index, as they are not needed.


Situation B: all tables in a dcoument


Dim oTable As Word.Table
If ActiveDocument.Tables.Count = 0 Then
Msgbox "Document contains no tables to action."
Exit Sub
Else
For Each oTable In ActiveDocument.Tables()
lngNbrOfColumns = oTable.Columns.Count
lngNbrofRows = oTable.Rows.Count
'Set variable for number of rows
lngRow = lngNbrofRows
'Process table
With oTable
' etc etc
End With
Next
End If
No use of Range or table index, as they are not needed.

[fumei]

Regarding multiple tables, if there was a situation where you wanted to process multiple tables, or all tables, but some of the tables you do NOT want to process, then determine the logic that differentiates them.

Something like:

Function ActionMe(oTable As Table) As Boolean
Dim strTest As String
Dim oCell As Cell
strTest = "gerry"
For Each oCell In oTable.Range.Cells
If InStr(oCell.Range.Text, "gerry") > 0 Then
ActionMe = True
Exit Function
Else
ActionMe = False
End If
Next
End Function

Sub LetsDoIt()
Dim oTable As Table
For Each oTable In ActiveDocument.Tables
If ActionMe(oTable) = True Then
' action THIS table of loop
End If
Next
End Sub
This checks each table, check each cell of the table for "gerry". If "gerry" is found in the table, the table is actioned. If it is not, the table is not actioned.

Further, regarding multiples, as your code stands:

Table 1
Table 2
Table 3
Table 4

Table 2 AND 3 are selected. The variable i = 3. Table 2 will not be processed.

[fumei]

And of course if you wanted to be able to put whatever search string you want:

Function ActionMe(oTable As Table, _
strIn As String) As Boolean
Dim oCell As Cell
For Each oCell In oTable.Range.Cells
If InStr(oCell.Range.Text, strIn) > 0 Then
ActionMe = True
Exit Function
Else
ActionMe = False
End If
Next
End Function