Pls see my code and issue below... I cannot get my Range(Left("A6", 3) code to work as I want it to do

[Beginner1200]

My Code Here is working fine! See further down

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Range(Left("A6", 3), Range(Left("A", 3) & Rows.Count).End(xlUp)).Offset(-1, 0).Sort Key1:=Range(Left("A6", 3)), Order1:=xlAscending, Header:=xlYes
    Application.Calculation = xlAutomatic
End Sub

Using the same code as above. when I am writing straight "140" it is sorting without any problems... but if write "140; testline" then the sorting is putting to line 20. and this is not my idea.
It seems that I cannot get my "Left code to work ???"
I have tried many things now and I cannot find any solution on this... ??????

 

[Michael M]

TRy changing to

Left(Range("A6"), 3)

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Your code appears to ThisWorkbook module code and will sort every time any cell/range in any worksheet in the workbook is selected, even if the data is not altered.
My guess is that you only want the data sorted when data is actually changed in column A, below row 5.
1. Is that correct? If not, please explain clearly in words what you are trying to do.

In any case you cannot sort by the left 3 characters in the cells in the manner that you are trying to do. It just 'happens' to work if you only have 3-digit numbers.
You will need to either have another column on the worksheet(s) to extract the left 3 characters from the column A values and sort on that or else take the values from column A into memory, have vba to sort the way you want and then put the sorted values back into the worksheet.
2. What would be your preference?

3. How many worksheets are there in the workbook?

4. Is there any data to the right of column A?

5. Which rows in your image above are you actually wanting to include in your sort?

There might be more questions later but answers to the above will give a bit clearer indication of the way forward.

 

[Beginner1200]

Thanks for replying on my post!!

I figured it out on my own... My code is working now!!

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A6:$N$6", Range("A" & Rows.Count).End(xlUp)).Offset(-1, 0).Sort Key1:=Range(Left("A6", 3)), Order1:=xlAscending, Header:=xlYes
Application.calculation = Automatic
End Sub

 

[RoryA]

Key1:=Range(Left("A6", 3)) is exactly the same as Key1:=Range("A6")

 

[Peter_SSs]

I figured it out on my own... My code is working now!!

That code does not work for me the way that you described in post #1.

Are your headers in row 6 as indicated in the post #4 code with the range starting in row 6 and headers xlYes or row 5 as shown in the original image in post #1?

Is the code in post #4 your actual code? Wondering due to Application.calculation = Automatic in post #4 as opposed to Application.Calculation = xlAutomatic in post #1?

 

[Beginner1200]

In row 5

 

[Peter_SSs]

In row 5

Thanks.

That did answer my other question but the code does not act for me as you described at the beginning. Anyway, if you are happy with how it is working, that's all that matters.