Sorting 10 rows without affecting other rows

peterkin · Mar 24th 2021

Hi all, I have a question when using sorting function in VBA.

I have a table which have around 30 rows.

I would like to sort the date of 10 rows within the table (say row 11-20 only) and do not want to affect other rows.

I created the following commend in VBA but fail to do so.

Code

Sub Test()

    With ActiveSheet.Sort.SortFields
        .Clear
        .Add Columns(3), Order:=xlAscending
        .Add Columns(2), Order:=xlDescending
        .Add Columns(1), Order:=xlAscending
    End With
    
    With ActiveSheet.Sort
        .SetRange ActiveCell
        .Header = xlNo
        .MatchCase = False
        .Apply
    End With

Display More

I selected rows 11-20 but it sorted the whole table.

How can I fix it?

Thank you

Kin

**Carim** · Mar 24th 2021

Hello and Welcome to the Forum

In the macro you posted there is no indication of a specific range ...

To create your macro, turn on the macro recorder ... and go through your process once ...

you will get a " VBA translation " of your instructions ... a good starting point ... which you can, then, improve on ...

Hope this will help

gijsmo · Mar 24th 2021

Maybe try the following.

Note that this has been set up to sort the whole row of the table (using K as the last column in this example)

Code

Sub Test()
  Const lStart = 11
  Const lEnd = 20
  
  With ActiveSheet
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range(.Cells(lStart, "C"), .Cells(lEnd, "C")), Order:=xlAscending
    .Sort.SortFields.Add Key:=.Range(.Cells(lStart, "B"), .Cells(lEnd, "B")), Order:=xlDescending
    .Sort.SortFields.Add Key:=.Range(.Cells(lStart, "A"), .Cells(lEnd, "A")), Order:=xlAscending
  End With
  
  With ActiveSheet
    'unless the aim is to really only sort rows 11-20 and cols A to C only
    'then this needs to be set to the actual first and last columns of the table,
    'columns K is used as an example
    .Sort.SetRange .Range(.Cells(lStart, "A"), .Cells(lEnd, "K"))
    With .Sort
      .Header = xlNo
      .MatchCase = False
      .Apply
    End With
  End With
End Sub

Display More

peterkin · Mar 24th 2021

Thank you very much. That's work

gijsmo · Mar 24th 2021

You're welcome

Sorting 10 rows without affecting other rows

Participate now!

Share

Tags