Sorting data by column values using Excel VBA, descending and ascending
<p>I have been working on a VBA macro that exports the values of a unit vector for each selected line in the SolidWorks interface, and I want to sort the value of the unit vector before it is written to Excel. I need to sort the Excel range B2 to D2 (B2 representing part i of the unit vector, C2 representing part j, and D2 representing part k), and the range depends on the number of selected lines. Each selected line represents a vector, and each vector represents a row in Excel.</p>

<p>I want to sort them by column D, and then I want to sort them with the maximum value in cell D2, the minimum value in cell D3, and have it go ascending from there. It would be ascending first, then descending from the second row down.</p>

<p>So if there were four selected lines, the D column would look like this:</p>

<p>-0.99221
-1
-0.99789
-0.99664</p>

<p>Currently it looks like this:</p>

<h3>Excel screenshot</h3>

<p><img src="https://i.stack.imgur.com/H3x19.png" alt="Excel screenshot"></p>

<p>But it would not know how many rows that needed to be sorted at first. Here is the relevant code:</p>

<pre><code>Dim r As Long
Dim ws As Worksheet
Set ws = ActiveSheet

For q = 3 To NumberOfSelectedItems
Columns("B:D").Sort key1:=Range("D2:D2"), order1:=xlDescending, Header:=xlYes
r = ws.Cells(ws.Rows.Count, 4).End(xlUp).Rows
Range("B3:D2" &amp; r).Sort key1:=Range("D2:D2"), order1:=xlAscending, Header:=xlNo
bRes = WriteToExcel(((q - 1) * 1) + 1, vModelSelPt4, "Unit Vector")
Next
</code></pre>

<p>Currently, the code gives me the 1004 error. I think it's something small that's wrong but am not sure. How do I order it by D column in the order described above?</p>

<pre><code>Private Function WriteToExcel(startRow As Integer, data As Variant, Optional label As String = "") As Boolean

    'get the results into excel

    With xlWorkbook.ActiveSheet
        .Cells(startRow + 2, 2).Value = data(0)
        .Cells(startRow + 2, 3).Value = data(1)
        .Cells(startRow + 2, 4).Value = data(2)
    End With
End Function
</code></pre>

<p>The above code is the function for writing the data to Excel; it is very relevant. This is all done in SolidWorks API so the syntax is a bit different.</p>