Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-22-2021, 11:11 AM
nmkhan3010 nmkhan3010 is offline Sort by String Length Windows 10 Sort by String Length Office 2016
Novice
Sort by String Length
 
Join Date: Feb 2020
Posts: 23
nmkhan3010 is on a distinguished road
Default

Hi,

Am having a document contains a table, i want to sort a text by descending order (string length large to small) based on a text in the last column, Please find a attachment for references.

Please sort a text by large to small in the same file and saved in a same path and please exclude the table headers first 3 rows.

Doc Type : .docx & .rtf.

File location is more dynamic and changes every time, it should be as a browse option to select a file from a desired location, it could be more benefited.

Please do the needful and your help is highly appreciated.

I want to sort by column no 3 but first 2 rows were not having same columns and getting error "Invalid column number", can anyone alter the below code according to my need.

Below code has to exclude the first 2 rows and it should be consider from 3rd row because from third it has a uniformity columns...



Below code source : 2 Smart Ways to Sort a Column of Texts by Length in Your Word - Data Recovery Blog

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
Attached Files
File Type: docx Input.docx (79.3 KB, 9 views)
Reply With Quote
  #2  
Old 11-01-2021, 01:50 PM
macropod's Avatar
macropod macropod is offline Sort by String Length Windows 10 Sort by String Length Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: Sort by String Length and Sort by String Length
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters

To do the sorting, you need to temporarily add a column to the table and add the string lengths to that, before sorting via the added column. For some code that does most of what you need, see: vba - How can I sort a word table by specific string, which is a time like HH:MM inside each line of the first column - Stack Overflow
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
descending, text length

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort by length.. ranjan Word VBA 1 09-03-2021 03:19 PM
Need to sort based on the length of cells ganesang Excel Programming 2 08-31-2021 09:58 PM
Sort by String Length Wildcard replace any string in context with a specified string wardw Word 7 05-07-2018 09:13 AM
Sort by String Length Take String of numbers, expand ranges, sort, then compress back into ranges AustinBrister Word VBA 19 08-22-2016 05:18 PM
Sort by String Length Way to search for a string in text file, pull out everything until another string? omahadivision Excel Programming 12 11-23-2013 12:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:11 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft