#1
|
|||
|
|||
Sort by length..
Hi,
Am having a table and i want to sort by largest to smallest by the column value 3. am having a code but am unable to modify the range as per my requirement. My range is first 3 rows has to be excluded from the table or range starts from the 4th row. Below Macro works pretty when it has a table structure and when i delete the first 3 rows of my table below code is working exactly, is it possible without deleting a first 3 rows it can sort the table by largest to smallest. PFA for reference. Code: Code:
Sub SortByWordLength() Dim objTable As Table Dim objColumnCell As Cell Dim objColumnCellRange As Range Dim objNewColumnCellRange As Range Dim nRowNumber As Integer Dim nColumnNumber As Integer Dim strWordLenth As String Dim nSortOrder As Integer Dim nCurrentTableIndex As Integer Dim nTableColumnsInCurrentTable As Integer nCurrentTableIndex = ActiveDocument.Range(0, Selection.Tables(1).Range.End).Tables.Count nTableColumns = ActiveDocument.Tables(nCurrentTableIndex).Columns.Count nColumnNumber = InputBox("Enter the column number you want to sort", "Column Number", "For example:2") If nColumnNumber > 0 And nColumnNumber <= nTableColumns Then nSortOrder = InputBox("Choose the sort order:" & vbNewLine & "If you want to sort by descending, click 1" & vbNewLine & "If you want to sort by ascending, click 0", "Sort Order", "For example:1") If nSortOrder = 1 Or nSortOrder = 0 Then ' Add a new column to put the word length of the specified column. Set objTable = ActiveDocument.Tables(nCurrentTableIndex) objTable.Columns.Add BeforeColumn:=objTable.Columns(nColumnNumber) nRowNumber = 1 For Each objColumnCell In objTable.Columns(nColumnNumber + 1).Cells Set objColumnCellRange = objColumnCell.Range objColumnCellRange.MoveEnd Unit:=wdCharacter, Count:=-1 Set objNewColumnCellRange = objTable.Cell(nRowNumber, nColumnNumber).Range objNewColumnCellRange.MoveEnd Unit:=wdCharacter, Count:=-1 strWordLenth = Len(objColumnCellRange.Text) objNewColumnCellRange.InsertAfter (strWordLenth) nRowNumber = nRowNumber + 1 Next objColumnCell objTable.Select ' Sort by the word length. Selection.Sort ExcludeHeader:=True, FieldNumber:="Column " & nColumnNumber, SortFieldType:= _ wdSortFieldNumeric, SortOrder:=nSortOrder, FieldNumber2:="", _ SortFieldType2:=wdSortFieldAlphanumeric, SortOrder2:=wdSortOrderAscending _ , FieldNumber3:="", SortFieldType3:=wdSortFieldAlphanumeric, SortOrder3:= _ wdSortOrderAscending, Separator:=wdSortSeparateByCommas, SortColumn:= _ False, CaseSensitive:=False, LanguageID:=wdEnglishUS, SubFieldNumber:= _ "Paragraphs", SubFieldNumber2:="Paragraphs", SubFieldNumber3:="Paragraphs" objTable.Columns(nColumnNumber).Delete Else MsgBox ("Invalid sort type, please try again") End If Else MsgBox ("Invalid column number, please try again") End If End Sub |
#2
|
||||
|
||||
Your code is inserting a temporary column to make a sortable list. Why not make two temporary columns and assign 0 to the first three cells, assign 1 to the rest and when you do your sort - do it on two columns.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to sort based on the length of cells | ganesang | Excel Programming | 2 | 08-31-2021 09:58 PM |
How to specify length of bar graph? | colt | Excel | 2 | 03-02-2017 11:43 AM |
Movie Length | helmerg | PowerPoint | 0 | 07-29-2013 12:23 PM |
page length | mzimpfer | Word | 2 | 10-23-2010 11:53 AM |
How to set the same length for different lines | myword29 | Word | 0 | 09-28-2007 08:12 PM |