Sort by Ascending Order in vba with Variable Last Row

Question

I am having a hard time analyzing the problem in my code. I need to sort ColumnE by ascending order but the problem is the rows that should not be included in the sorting was also sorted.

The highlighted in yellow should not be included in the sorting process. I need to start the sorting in row3.

Here is my code:

        'sort by ascending order
        wsRD.Columns("C:E").Sort key1:=wsRD.Range("E1"), _
        order1:=xlAscending, Header:=xlYes

Can anyone tell me what's wrong in my code? Appreciate your help.

Thank you.

If the highlighted cells are on the bottom of your data, instead wsRD.Columns("C:E") try wsRD.Columns("C3:E" & LastRow) being LastRow = wsRD.Range("C3").End(xlDown).Row - 1 — Damian
– Damian, Commented Oct 4, 2018 at 11:35
@Vityata because if the highlighted is on the last row, you don't want it to get on the sort range, so you get the -1 to leave it out the range. — Damian
– Damian, Commented Oct 4, 2018 at 11:49
Then you don't need the -1 you are right @Vityata but the code is the same. — Damian
– Damian, Commented Oct 4, 2018 at 11:55
Absolutely right @Vityata it's wsRD.Range("C3:E" & LastRow) being LastRow = wsRD.Range("C3").End(xlDown).Row — Damian
– Damian, Commented Oct 4, 2018 at 11:58

Matthijs Lasure · Accepted Answer · 2018-10-04 12:25:58Z

2

Your selection contains 2 header rows. You can either merge these headers, or set the range in your code as below (change the 9999 as needed):

    'sort by ascending order
    wsRD.Range("C2:E9999").Sort key1:=wsRD.Range("E2"), _
    order1:=xlAscending, Header:=xlYes

edited Oct 4, 2018 at 12:25

answered Oct 4, 2018 at 11:37

Matthijs Lasure

215 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vityata · Accepted Answer · 2018-10-04 11:42:42Z

The header in the example consists of two rows. And the Header:=xlYes expects only the first row to be a header, thus the second line is taken as part of the values to be sorted.

To fix it, use Range("C1:E" & lastRow5).Sort key1:=Range("E2"), order1:=xlAscending, Header:=xlYes, which takes the lastRow of the fifth column of the ActiveSheet with lastRow(ActiveSheet.Name, 5).

The whole solution:

Sub TestMe()

    Dim lastRow5 As Long
    lastRow5 = lastRow(ActiveSheet.Name, 5)
    Range("C1:E" & lastRow5).Sort key1:=Range("E2"), order1:=xlAscending, Header:=xlYes

End Sub

Function lastRow(wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet
    Set ws = Worksheets(wsName)
    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

Collectives™ on Stack Overflow

Sort by Ascending Order in vba with Variable Last Row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related