Sort by length

[YasserKhalil]

Hello everyone
I am trying to sort by legnth the values in column A using the code

Sub SortByLEN()
  With Range("B1:B10")
    .FormulaR1C1 = "=LEN(RC[-1])"
    .Sort Key1:=Range("B1:B10"), Order1:=xlDescending, Header:=xlNo
    .ClearContents
  End With
End Sub

It doesn't give any error but it doesn't do the task

Althouth if I gave up the With .. End With it works

Sub SortByLEN()
    Range("B1:B10").FormulaR1C1 = "=LEN(RC[-1])"
    Range("A1:B10").Sort Key1:=Range("B1:B10"), Order1:=xlDescending, Header:=xlNo
    Range("B1:B10").ClearContents
End Sub

What is the reason for that ..?

 

[NARAYANK991]

Hi ,

Try this :

Sub SortByLEN()
  With Range("B1:B10")
      .FormulaR1C1 = "=LEN(RC[-1])"
      Range("A1:B10").Sort Key1:=Range("B1:B10"), Order1:=xlDescending, Header:=xlNo
      .ClearContents
  End With
End Sub

You can figure out the reason by comparing this version with the original version.

Narayan

 

[Hui]

The Range in the first code is With Range ("B1:B10")

In the second code it is A1:B10

 

[YasserKhalil]

Oops You are right Mr. Hui
I didn't pay attention to the range("A1:B10") .. That's my fault
Thanks a lot for both of you for quick help
This is the final form

With Range("A1:B10")
        .Offset(, 1).FormulaR1C1 = "=LEN(RC[-1])"
        .Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlNo
        .Offset(, 1).ClearContents
    End With