repeat or chain a macro?

[t.a.smith]

I have simple macro I recorded for a table where I want to have it remove the
first name only in the cell, leaving the last name in the same cell. It goes
like this -

Sub delfn()
'
' delfn Macro
' Macro recorded 10/13/2009 by Teresa Smith
'
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
Selection.TypeBackspace
Selection.MoveDown Unit:=wdLine, Count:=1


End Sub

However, How do I get it to repeat itself all the way down the first column
of a table? I thought there is a way to chain it to keep running or
repeating? Can you help?

 

[Greg Maxey]

Teresa,

Recorded macros have their limits. You might try:

Sub ScracthMacro()
Dim oTbl As Word.Table
Dim i As Long
Set oTbl = Selection.Tables(1)
For i = 1 To oTbl.Rows.Count
oTbl.Cell(i, 1).Range.Words.First.Delete
Next i
End Sub

Put your cursor in the table and run the macro. If you have first and last
names in column 1 then it will delete the first names.

However, if you have entries such as Tom T. Hall then it will leave T. Hall.
With that in mind you may want to try:

Sub ScracthMacroII()
Dim oTbl As Word.Table
Dim i As Long
Dim j As Long
Set oTbl = Selection.Tables(1)
For i = 1 To oTbl.Rows.Count
j = oTbl.Cell(i, 1).Range.Words.Count
oTbl.Cell(i, 1).Range.Text = oTbl.Cell(i, 1).Range.Words(j - 1)
Next i
End Sub

I have simple macro I recorded for a table where I want to have it
remove the first name only in the cell, leaving the last name in the
same cell. It goes like this -

Sub delfn()
'
' delfn Macro
' Macro recorded 10/13/2009 by Teresa Smith
'
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
Selection.TypeBackspace
Selection.MoveDown Unit:=wdLine, Count:=1


End Sub

However, How do I get it to repeat itself all the way down the first
column of a table? I thought there is a way to chain it to keep
running or repeating? Can you help?

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[t.a.smith]

This sub routine worked perfectly!! You are a genius in my book.
Thank you.

 

[Jay Freedman]

I have simple macro I recorded for a table where I want to have it remove the
first name only in the cell, leaving the last name in the same cell. It goes
like this -

Sub delfn()
'
' delfn Macro
' Macro recorded 10/13/2009 by Teresa Smith
'
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
Selection.TypeBackspace
Selection.MoveDown Unit:=wdLine, Count:=1


End Sub

However, How do I get it to repeat itself all the way down the first column
of a table? I thought there is a way to chain it to keep running or
repeating? Can you help?

This illustrates a couple of limitations of the macro recorder -- it
has no way to write code for repeating operations, and it only knows
how to pretend it's a "ghost typist" hitting keys. If you learn a
little bit about the VBA language and how it represents things in Word
documents, you can make it do what you want without these limitations.
I'll suggest trying
http://word.mvps.org/FAQs/MacrosVBA/VBABasicsIn15Mins.htm.

This code works as you intend:

Sub demo()
Dim myCell As Cell
If Selection.Information(wdWithInTable) Then
For Each myCell In Selection.Tables(1).Columns(1).Cells
myCell.Range.Words(1).Delete
Next
End If
End Sub

This is what it does:

- The Dim statement creates a variable that represents a single table
cell.

- The If statement makes sure the cursor is inside a table; if it
isn't, then execution jumps down to the End If and nothing happens.

- The For statement does the repetition. It assigns each cell in the
first column of the table, one at a time, to the myCell variable.
There's more explanation of this below.

- The myCell.Range statement deletes the first word in the cell,
including the space after the visible characters.

- The Next statement sends execution back up to the For statement,
where the next cell in the column is assigned. When there are no more
cells to process, the execution goes down to the End If, and from
there to the End Sub.

The meaning of the expression Selection.Tables(1).Columns(1).Cells is
this: Because the If statement was True, we know that the Selection
(the cursor) is in a table. The first part, Selection.Tables(1),
represents that table. Then Selection.Tables(1).Columns(1) is the
first column of the table, so Selection.Tables(1).Columns(1).Cells is
the collection of cells in that column.

Similarly, myCell.Range.Words(1) represents the first word in the cell
represented by myCell.

 

[Greg Maxey]

Jay,

You are a genius in my book ;-).

This illustrates a couple of limitations of the macro recorder -- it
has no way to write code for repeating operations, and it only knows
how to pretend it's a "ghost typist" hitting keys. If you learn a
little bit about the VBA language and how it represents things in Word
documents, you can make it do what you want without these limitations.
I'll suggest trying
http://word.mvps.org/FAQs/MacrosVBA/VBABasicsIn15Mins.htm.

This code works as you intend:

Sub demo()
Dim myCell As Cell
If Selection.Information(wdWithInTable) Then
For Each myCell In Selection.Tables(1).Columns(1).Cells
myCell.Range.Words(1).Delete
Next
End If
End Sub

This is what it does:

- The Dim statement creates a variable that represents a single table
cell.

- The If statement makes sure the cursor is inside a table; if it
isn't, then execution jumps down to the End If and nothing happens.

- The For statement does the repetition. It assigns each cell in the
first column of the table, one at a time, to the myCell variable.
There's more explanation of this below.

- The myCell.Range statement deletes the first word in the cell,
including the space after the visible characters.

- The Next statement sends execution back up to the For statement,
where the next cell in the column is assigned. When there are no more
cells to process, the execution goes down to the End If, and from
there to the End Sub.

The meaning of the expression Selection.Tables(1).Columns(1).Cells is
this: Because the If statement was True, we know that the Selection
(the cursor) is in a table. The first part, Selection.Tables(1),
represents that table. Then Selection.Tables(1).Columns(1) is the
first column of the table, so Selection.Tables(1).Columns(1).Cells is
the collection of cells in that column.

Similarly, myCell.Range.Words(1) represents the first word in the cell
represented by myCell.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[Gordon Bentley-Mix]

You're both geniuses in my book! It's called "Geniuses I Have Known"... ;-D

 

[Jay Freedman]

And to both of you I say, "Flattery will get you pretty far."

The code is elementary. The effort is in trying to explain how to
design it.

 

[Greg Maxey]

I have always enjoyed and appreciated the ride. Thanks Jay.

And to both of you I say, "Flattery will get you pretty far."

The code is elementary. The effort is in trying to explain how to
design it.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR