Home |
Search |
Today's Posts |
#1
|
|||
|
|||
select rows until empty found
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). -- David |
#2
|
|||
|
|||
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. "David" wrote in message ... 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). -- David |
#3
|
|||
|
|||
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 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!! -- David |
#4
|
|||
|
|||
"David" wrote in message ... 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 Perfect!!! I changed: With Selection.Tables(1) to With ActiveDocument.Tables(1) 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 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. Thus avoiding two of the potential pitfalls you mentioned. Many thanks!! -- David |
#5
|
|||
|
|||
Jezebel wrote
and added as first line: Selection.HomeKey Unit:=wdStory 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. 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. -- David |
#6
|
|||
|
|||
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. -- David |
#7
|
|||
|
|||
Yelling? I was just puzzled.
"David" wrote in message ... 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. -- David |
#8
|
|||
|
|||
Jezebel wrote
Yelling? I was just puzzled. "David" wrote in message ... 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. -- David 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. -- David |
#9
|
|||
|
|||
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. 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. -- David |
#10
|
|||
|
|||
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. Got it sorted (so to speak). Replaced SEQ nums fields with customized numbering. Side benefit: no longer have to update fields after sort. -- David |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible BUG: Multiple HTTPrequests send from one includetext statement | Mailmerge | |||
How do I turn select text on and use Find to select a block of te. | Microsoft Word Help | |||
automatically remove empty rows in a table | Microsoft Word Help | |||
2002 Word Table Rows | Tables | |||
Excel Link: Adding rows in Excel does not add cells in Word | Tables |