Sorting with hidden prefix not working as intended

bookie · #1 05-19-2015, 09:13 AM

Hi all

I am trying to sort a number of catalog entries, and have been helped immensely by this thread (https://www.msofficeforums.com/word/...rd-2007-a.html) on sorting paragraphs, but I've run into a confounding problem.

I have a series of catalog entries that I am trying to sort. Some have "subject" headers in brackets and others simply are headed with the author, as below.

HTML Code:

zxc. 	Marie, A., Dr. Pellagra ... 
zxc. 	[Carver, George Washington.] Merritt, Raleigh H. From captivity to fame: 
zxc. 	[Methodists.] Anderson, James A. Centennial history of ...

I have been able to get as far as sorting everything, except the brackets are always organized first, and then the rest of the catalog follows. I have tried making hidden all of the text before the first letter of the subject header, and this has for some reason not solved the problem. When hidden text is not shown I see:

HTML Code:

Carver, George Washington.] Merritt, Raleigh H. From captivity to fame: 
Methodists.] Anderson, James A. Centennial history of ...
Marie, A., Dr. Pellagra ...

But "Marie" etc. is still coming behind "Methodists." I did a smaller test case where I made a short list, hid the brackets, and sorted, and things worked fine, so I don't know why in this particular case the sort is not working.

Here is the macro I have. My programming skill is limited, so apologies for what may be messy code. I've been find/replacing the hidden text outside of the macro so far.

HTML Code:

Sub Sorttest()
'
Application.ScreenUpdating = False
Dim Tbl As Table
With ActiveDocument.Range
 With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchWildcards = True
    .Text = "^13^13"
    .Replacement.Text = "|"
    .Execute Replace:=wdReplaceAll
    .Text = "^13"
    .Replacement.Text = "~"
    .Execute Replace:=wdReplaceAll
  End With
  Set Tbl = .ConvertToTable(Separator:="|", NumColumns:=1)
   With Tbl
    .Sort ExcludeHeader:=False, FieldNumber:="Column 1", CaseSensitive:=False, _
      SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending
    .Rows.ConvertToText Separator:=wdSeparateByParagraphs
  End With
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchWildcards = True
    .Text = "~"
    .Replacement.Text = "^13"
    .Execute Replace:=wdReplaceAll
  End With
End With

End Sub

bookie · #2 05-19-2015, 01:55 PM

I've managed to find a solution, so in case anyone else ends up in the same situation here it is.

I did a find/replace for "zxc. [" > "ZXC. "

The sort doesn't distinguish between capitals so everything is properly sorted after that. I then can do a search matching case to replace the opening bracket. I still don't know why hiding the text didn't work, but this workaround solved that problem.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA to keep hidden rows hidden when filtering	tectonicseer	Excel Programming	1	07-27-2014 11:54 AM
VBA is objecting (no pun intended)	Ulodesk	Word VBA	2	06-19-2013 08:46 AM
Add Standard Prefix To Cells	zulhfreelancer	Excel	1	06-14-2013 04:12 PM
Hidden style applied over already-hidden text.	christie	Word	1	08-17-2011 09:10 AM
CAUTION!! Sorting a spreadsheet with hidden columns will trash your data.	psmaster@earthlink.net	Excel	0	11-24-2009 11:54 AM