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

A possibility could be to use a dummy column to help to sort.
In column AC put next formula
AC24=CODE(C24)
In column "Name" change formula with
=IF(E30=0,CHAR(143),E30)
see the use of CHAR(124) istead of ''
And use next macro

Option Explicit


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


    ActiveSheet.Unprotect
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("AC24:AC100"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B24:AC100")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[PurrSent]

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

A possibility could be to use a dummy column to help to sort.
In column AC put next formula
AC24=CODE(C24)
In column "Name" change formula with
=IF(E30=0,CHAR(143),E30)
see the use of CHAR(124) istead of ''
And use next macro

Option Explicit


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


    ActiveSheet.Unprotect
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("AC24:AC100"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B24:AC100")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Hi PCL

Many thanks for your response. I've been trying to get my head around your explanation and VBA but just can't understand it. I can understand using a 'dummy' column but confused regarding -

AC24=CODE(C24) - I've google'd CODE and can't say I really understand this

In column "Name" change formula with
=IF(E30=0,CHAR(143),E30) - I've google'd CHAR stuff decided it's too complicated for me

I think, if I understood where "E30" comes into this, I might be able to accept this better. It's certainly not that I'm questioning what you've suggested as you're clearly far more experienced and knowledgeable in this. I'm very much limited it my knowledge, of course.

Column 'E' in my spreadsheet is the 'Gross' column, totaling the monies entered along the row.

Also, would I assume to put "=IF(E30=0,CHAR(143),E30)" in the 'Name' column of the same row? Will it still bring forward the entry from the previous month?

I'm really sorry for being so dense. I know so very little regarding this more advanced stuff, although I enjoy learning about it and how to use it

Perhaps you would have the time and patience to explain this all a bit more for me? I'd be very grateful.

Again many thanks

 

[PCL]

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

You have first to change the formula in column "Name" using =IF(E30=0,CHAR(143),E30) here for row 30.
This will put a character not visible instead of nothing ( "" in your formula)

In column AC put next formula =CODE(C24) (here for row 24) which return the ASCII code of the first character in column C
The new macro is using column AC to sort data

Have you tested the macro and formulas, does it gives the right result ???

 

[PurrSent]

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

You have first to change the formula in column "Name" using =IF(E30=0,CHAR(143),E30) here for row 30.
This will put a character not visible instead of nothing ( "" in your formula)

In column AC put next formula =CODE(C24) (here for row 24) which return the ASCII code of the first character in column C
The new macro is using column AC to sort data

Have you tested the macro and formulas, does it gives the right result ???

Hi PCL

I've entered the formula into 'Name' into row 30 and entered =CODE(C24) into AC24. I've run these formulas up/down the rows to copy them through. On running the macro, I now have column 'C' containing numbers and column AC also showing numbers. So, no, I don't feel this has worked, I'm afraid.

Sorry.

 

[PurrSent]

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

PCL
Thanks very much for all your help with this
I'm sorry it has not been solved but you've certainly given it a shot
Thanks again
%

 

[PCL]

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

You're welcome
Please to help ... at my level ...!

 

[PurrSent]

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

Thanks P
I'm sure I'll be posting again, before too long
Many thanks for all your help
J