Macro - Sort Column/Range Alphabetically

[PurrSent]

This is, in fact, related to, and follows on from, a previous post - https://www.mrexcel.com/forum/excel-questions/1095651-macro-sort-column-range-alphabetically-taking-rows-them.html

Summary
I have recorded a Macro to sort a column (C) within Range("B24:AF218") to sort Column 'C' alphabetically. I'm very grateful for help from acinDean who has helped me get this far.

Range("B24:AF218")

Sub Macro4()
'
' Macro4 Macro
' Sort Name Colume 'C' - A-Z
'

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

And now ...
I could really do with some help, please, with adjusting this Macro so that it can be used on any 'active sheet', and not only March (which is the sheet I recorded on) I have sheets Jan thru to Dec. I have looked and looked at this code to try to work out how to adjust it but it's way beyond my capabilities

Also, using this Macro, column 'C' is sorted alphabetically BUT, where there is data, ie 'x' or '0' in 'B' alongside an empty cell in 'C', this row is sorted to the top of the section instead of, preferably, at the lower end of the sorted list.

Many thanks to anyone who can help me with this issue. I'll be very grateful

 

[PCL]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

If you want

adjusting this Macro so that it can be used on any 'active sheet'

just delete next Statement

Sheets("Mar").Select

 

[PurrSent]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

If you want

just delete next Statement

Sheets("Mar").Select

Hi and thanks

There seems to be more than one instance where 'Mar' is quoted, so can I delete them all?

Any suggestions regarding the '0' 'x' '22' & '29' being at the lower part of the sorting instead of at the top, please?

Many thanks

 

[PCL]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

There seems to be more than one instance where 'Mar' is quoted, so can I delete them all?

Yes

regarding the '0' 'x' '22' & '29' being at the lower part of the sorting instead of at the top

Can you check that in column "C" cells at the top are really empty and containing a space

 

[PurrSent]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

Thanks again
Yes, in fact, there is a formula in each of those 'blank' cells in 'C'
The formula is in there to bring forward the contents of the equivalent cell in previous month. Assuming the previous month has been completed, there is little/no need for the formula to remain. Seems as though I have to accept these 'hidden' formulae will cause some rows to appear at the beginning of a sort. Thanks.

I've removed all references to 'Mar' sheet but now the macro doesn't work. I've obviously not done something right when removing the references. Would you be able to (and prepared to) check the code below and correct it for me, please (please, please ) I would very much appreciate it. It's way over my head, this
Many thanks

Range("B24:AF218")

Sub Macro4()
'
' Macro4 Macro
' Sort Name Colume 'C' - A-Z
'

'
   
    ActiveSheet.Unprotect
    Range("B24").Select
    ActiveWindow.SmallScroll Down:=50
    Range("B24:AB100").Select
    ActiveWorkbook.Worksheets ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets ActiveSheet.Sort.SortFields.Add Key:=Range("C24:C100"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets 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

 

[PCL]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

Try next code

Option Explicit


Sub Macro4()
'
' Macro4 Macro
' Sort Name Colume 'C' - A-Z


'    Sheets("Mar").Select
    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
'    Sheets("Mar").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("C26").Select
End Sub

 

[PurrSent]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

Brilliant!!! It's working well now

The cells in 'C' with formula in still come to the top but the rows below those are in alphabetical order. Unless there is a reasonably easy solution, I think this will suffice.

Many thanks, PCL, for helping me, and sparing the time, to get this macro working.

I'm gradually learning more regarding VBA but have a long way to go before I'm likely to sort this sort of thing out I'll keep working on it, tho

Thanks again. I really do appreciate your help, especially as you have been so prompt in responding

%

 

[PCL]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

The cells in 'C' with formula in still come to the top

Can you post the formula ?

 

[PurrSent]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

Thanks for this help ...

Here's a snip of the latest sort, followed by snip of the formulas in same cells

Showing Formula

 

[PurrSent]

Re: Macro - Help needed, pls - Sort Column/Range Alphabetically

Bump!
.. Coz no response so far since 3 days
Thanks