Macro to Sort Column Alphabetically not working correctly

[PurrSent]

Using Excel 2010 & 2007
Windows 10

I would really appreciate some more help regarding a macro, please.

I've had help to get this macro but, (as in a similiar issue with a macro to sort column in date order - since solved) when I run the macro to sort Column 'C', containing names, into alphabetical order, the associated rows don't always keep the same sorted order.

In other words, the data that has been entered along the row, in relation to a name, isn't always kept to that associated row, so the entered figures are not always pertaining to the details entered in Columns 'C', 'D', 'E', 'F' etc.

This is my current code

 Sub SortName()
'
' Sort Name Column 'C' - A-Z

    ActiveSheet.Unprotect
 '   Range("B24").Select
 '   ActiveWindow.SmallScroll Down:=50
    Range("B24:AB100").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("C24:C100"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B24:AB100")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("C26").Select
End Sub

The previous issue, re sorting the date, was solved in post here - https://www.mrexcel.com/forum/excel-questions/1079390-macro-sort-dates.html
It may help to explain what I'm probably not explaining well The code below was allowing the rows to be randomly mixed

 Sub Date_Order_1()
  '
  ' Date_Order Macro
  ' Puts Date (Column B) in ascending order
  '
  ' Keyboard Shortcut:
     
     ActiveSheet.Unprotect Password:=""
     
     With ActiveSheet
       .Unprotect
       .[A16:BZ115].Sort Key1:=[B16], Order1:=xlAscending
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     End With
        ActiveSheet.Protect Password:=""
  End Sub

The code here (below), provided by 'yky' solved the problem.

"Try this: "

Sub Date_Order_1()
....
       .Range("A16:BZ115").Sort Key1:=Range("B16"), Order1:=xlAscending

I must admit it took me some time and a lot of comparing etc to see the difference between the code I was using and the code yky suggested but, once I'd seen where it differed and I edited the code, it worked perfectly.

I've had a good look at both versions of code and tried to work out where I might edit the code for the Sort Alphabetically macro but it's really got me stumped.

I'm hoping something similiar will solve the Sorting Alphabetically macro too, and hoping one of you will be able to help me out

Many thanks for any help and advice
%

 

[Logit]

Re: Help Required pls - Macro to Sort Column Alphabetically not working correctly

.
This should take care of the SORT requirement.

Option Explicit


Sub srtMe()
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("B24:AB100" & LastRow).Sort Key1:=Range("B24"), _
       Order1:=xlAscending, Header:=xlNo
End Sub

 

[PurrSent]

Re: Help Required pls - Macro to Sort Column Alphabetically not working correctly

Hi Logit

Thanks for your response.

I've just been trying this in a copy of the worksheet but I'm getting a couple of different errors. I then wondered if it was because the sheets are protected so I added some code (which has worked OK in other macros on this workbook). As you will see, there is no password. I still got the same messages - see images below

Sub srtMe()
ActiveSheet.Unprotect Password:=""
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("B24:AB100" & LastRow).Sort Key1:=Range("B24"), _
       Order1:=xlAscending, Header:=xlNo
End
        ActiveSheet.Protect Password:=""
End Sub

There are no merged cells within the range B24:AB100)
Also, probably academic at this point, the range should be B24:AB218 but presumably I can amend that once this vba is running ok?
Should the range include Column 'A'? It contains character '1' for the purpose of another macro (and I've forgotten why just now )

If it's of any help to you, this same problem re rows not being sorted properly, with Date Sorting and the solution was

Also, (due to my naivety and lack of understanding code) within the code, where is it instructing the sort to be in Column 'C' please? Without understanding this, I stand no chance of learning

I really appreciate your help. Thanks very much
%

 

[Logit]

Re: Help Required pls - Macro to Sort Column Alphabetically not working correctly

.
My guess is something is corrupt in your workbook. I guess it is possible
but I've never experienced Excel to warn about merged cells unless they existed.
Merged cells usually create an issue at some time ... always best to avoid their use.

If you can post your workbook on a cloud site (DropBox.com, Google, etc.) for download and provide a link here .. that
would be the best method of determining what is happening with the workbook.

 

[Fluff]

Re: Help Required pls - Macro to Sort Column Alphabetically not working correctly

It should be

Range("B24:AB" & LastRow).Sort Key1:=Range("B24")

 

[Joe4]

Re: Help Required pls - Macro to Sort Column Alphabetically not working correctly

Shouldn't this part:

Range("B24:AB[COLOR=#ff0000]100[/COLOR]" & LastRow).Sort...

actually be this?

Range("B24:AB" & LastRow).Sort...

If you are calculating the last row, you want to remove the "100" from the current last row part, otherwise you may be choosing a number that exceeds the maximum number of lines on a single Excel page.

For example, if LastRow was 100000, your code would actually calculate to:

Range("B24:AB100100000").Sort...

which exceeds Excel's maximum of 1048576 rows per page.

 

[PurrSent]

Re: Help Required pls - Macro to Sort Column Alphabetically not working correctly

Thanks guys for all your responses

Apologies, but things have been a bit hectic here over the past few days so not had time to do anything with the spreadsheet
However, things should quieten down over the next couple of days so I'll be able to spend some time on this
I'll respond 'properly' after that

Again, many thanks
%

 

[PurrSent]

Re: Help Required pls - Macro to Sort Column Alphabetically not working correctly

Apologies again

Unfortunately, this issue ended up on the 'back burner's. Now, as far as I know, the user of the spreadsheet doesn't seem to be having the problem / or not using this 'Sort' so maybe I won't need to follow it up further.

If I do need to bring it up again, I think it will be best to start a new thread, largely as I will have forgotten where I left this thread

However, I really appreciate the help you all gave. No doubt I'll be back on the forum, in due course, with other issues needing your help and advice

Many, many thanks
%