Excel Sorting one column by Largest to Smallest string

Question

I am trying to sort each of my excel files by one column that has large length text. This is so the data isn't truncated when imported into SQL using data services job. I realise Excel reads the first 8 or 16 lines and determines the length of field based on that, a lot of my text was getting cut at 255 characters.

I tried one file as an experiment and the column was set at 399 varchar, which is the length of the longest string in one of the files. It doesn't matter what size of field I set in DS job mapping, the length of string experienced sets field size.

I have created a little snippet of VBA to loop through each file and sort by largest to smallest text, it is looping through and is doing something but is not sorting largest to smallest.

Not sure if it is empty cells or what that is causing it, however I managed to do this manually for one file - sort largest to smallest - now it never says largest to smallest, only A > Z for some reason.

I have one issue with the code, and that is as I mentioned it is not sorting, and 2) I have one concern about processing multiple files and determining max field length. I don't know if the DS job will alter the field size if it comes across another file with a larger text length than the last one? I done this with one file, but that doesn't tell me, the only way would be to try and properly sort multiple files, then run the job against each file.

Here is the loop wher eI am trying to also do the sort. I find the column called "sort Me" as an example then apply the sort to it.

 For i = 1 To lastcol

        With wb.ActiveSheet
            ColChar = colLtr(i)
            rangestr = ColChar & "1:" & ColChar & "" & MaxRowCount
             If .range(ColChar & 1).Value = "Sort Me" Then
                range(rangestr).Sort key1:=range(rangestr), order1:=xlDescending, Orientation:=xlSortRows, Header:=xlYes
            End If
        End With

    Next i

I have a function that creates the column character from the column number. I loop through each column till I hit the one I want then try and sort. the rangestr variable is the range I want to sort.

EDIT: To clarify I want text field longest at top and shortest at bottom, there are, nulls, some sheets have no text. Like this:

"This is the longest piece of text to be a top"
"This is shorter piece of text"
"This is even shorter"

Mainly would at least like to get the sorting done properly first. Seeking suggestions on how to do that properly.

Many thanks

Andrew

Answer 1

Excel has powerful built in sorting function that I'd preferably use for both their actual speed and not to reinvent the wheel

given you need a new (and temporary) field - one with text length in it - I'd also use a "helper" field approach and code as follows:

Sub SortIt(dataRng As Range, headerStrng As String)
    Dim f As Range, helpRng As Range
    Dim colsOffset As Long

    With dataRng
        Set f = .Rows(1).Find(what:=headerStrng, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) '<--| look for wanted header column
        If f Is Nothing Then Exit Sub '<--| if no header found, then no job to do!

        Set helpRng = .Resize(, 1).Offset(, .Parent.UsedRange.Columns(.Parent.UsedRange.Columns.Count).Column - .Columns(1).Column + 1) '<--| set a helper range in first column outside worksheet used range and occupying data range same rows
        With helpRng
            colsOffset = .Column - f.Column + 1 '<--| calculate column offset from "header" column to "helper" range
            .FormulaR1C1 = "=len(RC[-" & colsOffset - 1 & "])" '<--| fill "helper" range with corresponding "header" cells number of characters. they will be eventually cleared
        End With
        .Resize(, helpRng.Column - .Columns(1).Column + 1).Sort key1:=helpRng, order1:=xlDescending, Orientation:=xlSortColumns, Header:=xlYes
        helpRng.Clear '<--| clear the "helper" range
    End With
End Sub

to be called by your main sub as follows:

Option Explicit

Sub main()
    Dim dataRng As Range, headerStrng As String

    With Worksheets("SortData") '<--| change "SortData" with your actual sheet name
        Set dataRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 10) '<--| change "A1", "A" and '10' to reflect, respectively, your  data actual leftupmost cell, "counter" column (i.e. the one that determines its rows span) and columns number
    End With
    headerStrng = "Sort me" '<--| change "Sort me" with your actual header

    SortIt dataRng, headerStrng
End Sub

Answer 2

Based on a code snippet which I found here
I edited the code as shown below, just pass in the range you want to sort and set the optional parameter to true if you want to sort by shortest length

Sub SortByLength(rangeToSort As Range, Optional shortest As Boolean = False)
    Dim x As Long, y As Long, lLastRow As Long
    Dim tempX As String, tempY As String
    Dim tempArray As Variant

    tempArray = rangeToSort
    'Sort array

    For x = 1 To UBound(tempArray)
        For y = x To UBound(tempArray)
            If shortest = True Then
                If Len(tempArray(y, 1)) < Len(tempArray(x, 1)) Then
                    tempX = tempArray(x, 1)
                    tempY = tempArray(y, 1)
                    tempArray(x, 1) = tempY
                    tempArray(y, 1) = tempX
                End If
            Else
                If Len(tempArray(y, 1)) > Len(tempArray(x, 1)) Then
                    tempX = tempArray(x, 1)
                    tempY = tempArray(y, 1)
                    tempArray(x, 1) = tempY
                    tempArray(y, 1) = tempX
                End If
            End If
        Next y
    Next x
    'Output sorted array
    Range(rangeToSort.Item(1), rangeToSort.Item(UBound(tempArray))) = (tempArray)

End Sub