select rows until empty found

D

David

Posted this once, but it went to Neverland :(
Repeating, but not exact wording because I can't remember all I said.
Trying to automate via macro the following (Word2000, one table):

1. Start selecting rows with row 2 (Headers in row 1)
2. Continue until column 2 is empty
3. Sort ascending on column 2 (text entries)
4. Update fields in column 1 (SEQ nums)

Currently users select populated rows with mouse, do the sort, and with
selection still in effect, update fields with F9

I want them to click a custom macro button to do this (I know how to set
the button with macro in a toolbar).
 
J

Jezebel

Something along these lines

Dim pIndex as long
Dim pRange as Word.Range

pIndex = 2
With Selection.Tables(1)

'Find row with empty cell in column 2
Do until len(.Cell(pIndex,2).range.text) = 2
pIndex = pIndex + 1
Loop

'Define the Range of interest
Set pRange = ActiveDocument.Range(.Rows(2).Range.Start,
..Rows(pIndex-1).Range.End)

end with

'Sort it
pRange.Sort FieldNumber:="Column 2"

'Update fields
pRange.Fields.Update


If you're running this a lot, or you're giving it to others to run, you
should add error checking. As it is the code will fail if there are no empty
cells in column 2, or if column 2 row 2 is empty, or the table is irregular
(merged or split cells), or the selection is not in a table to start with.
 
D

David

Jezebel wrote
Dim pIndex as long
Dim pRange as Word.Range

pIndex = 2
With Selection.Tables(1)

'Find row with empty cell in column 2
Do until len(.Cell(pIndex,2).range.text) = 2
pIndex = pIndex + 1
Loop

'Define the Range of interest
Set pRange = ActiveDocument.Range(.Rows(2).Range.Start,
.Rows(pIndex-1).Range.End)

end with

'Sort it
pRange.Sort FieldNumber:="Column 2"

'Update fields
pRange.Fields.Update
Click to expand...

Perfect!!!

I changed:
With Selection.Tables(1)
to
With ActiveDocument.Tables(1)

and added as first line:
Selection.HomeKey Unit:=wdStory

Thus avoiding two of the potential pitfalls you mentioned.

Many thanks!!
 
J

Jezebel

David said:
Jezebel wrote


Perfect!!!

I changed:
With Selection.Tables(1)
to
With ActiveDocument.Tables(1)
Click to expand...

This avoids the first problem provided the table you want to work on is
indeed the first table in the document.


and added as first line:
Selection.HomeKey Unit:=wdStory
Click to expand...

Don't understand what you have in mind with this. Doesn't help with any of
the problems I mentioned. If you want to deal with errors, add an On Error
Goto statement and do it thoroughly ... but if the code is for your own use,
simply letting the code fall over is quite OK.
 
D

David

Jezebel wrote
Don't understand what you have in mind with this. Doesn't help with
any of the problems I mentioned. If you want to deal with errors, add
an On Error Goto statement and do it thoroughly ... but if the code is
for your own use, simply letting the code fall over is quite OK.
Click to expand...

I apologize. I misspoke on this one (you didn't mention this as a potential
problem).

Typically the user will click the custom button immediately after entering
data in the first empty row. I noticed in my initial test run (after
changing an existing name in Col 2 and leaving the cursor where it was)
that the sort didn't happen unless I first positioned the cursor in the
first cell of the table. Rather than require the user to remember to do
anything before activating the macro, I chose to position the cursor at the
beginning of the document to take the guesswork out. And since there is
only one table, as stated in my original post, the routine, as modified,
works as desired.
 
D

David

Jezebel wrote

---snip

Ok, Ok, before you yell at me again! Further testing reveals that the only
time a sort doesn't happen is the first time I open Word and the file and
don't leave the row I alter. Second click on the button sorts, then it
doesn't matter as long as I'm within the table.
 
D

David

Jezebel wrote
Yelling? I was just puzzled.
Click to expand...

Sorry. Guess I was being defensive after reading:
"...add an On Error Goto statement and do it thoroughly ... but if the code
is for your own use, simply letting the code fall over is quite OK.

Further strangeness discovered, though:

Seems my code going to beginning of document (or even anywhere else in the
table) prior to running the macro doesn't circumvent this scenario. "double
run" required *only* when I *first open Word*. Just closing and reopening
the file, things work fine. Have to close Word. Weird.

Try it. Save a test file after populating a few rows and adding the macro,
close and reopen Word, make a change or addition, run the macro (same
behavior here with yours OR mine). Ideas to fix this welcome.
 
D

David

David wrote
Further strangeness discovered, though:

Seems my code going to beginning of document (or even anywhere else in
the table) prior to running the macro doesn't circumvent this
scenario. "double run" required *only* when I *first open Word*. Just
closing and reopening the file, things work fine. Have to close Word.
Weird.

Try it. Save a test file after populating a few rows and adding the
macro, close and reopen Word, make a change or addition, run the macro
(same behavior here with yours OR mine). Ideas to fix this welcome.
Click to expand...

Ok, now I've narrowed it after finding this wasn't true with other files.
Seems to be something about having fields in Column 1 that keeps sort
routine from working the first run after opening Word.
 
D

David

David wrote
Ok, now I've narrowed it after finding this wasn't true with other
files. Seems to be something about having fields in Column 1 that
keeps sort routine from working the first run after opening Word.
Click to expand...

Got it sorted (so to speak). Replaced SEQ nums fields with customized
numbering. Side benefit: no longer have to update fields after sort.
 
Top