Macro to Sort Column/Range Alphabetically, taking rows with them

[PurrSent]

Hi

Using Windows 10; Excel 2010

Would someone be able to help me with a macro, please? I have a number of columns in a spreadsheet and I would like to be able to sort one column (column 'C') with 4 specific ranges (ie rows 24:218; 221:320; 344:444 & 447:547)

It's also necessary for the whole rows to be kept intact, as data in other column is relevant to the name in cell/column to be sorted of that row.

The spreadsheet is also 'Protected' although the password is blank, ie "" but I feel I will be able to add the necessary code to accommodate this, if required.

I have tried to understand some articles found via Google but, as always, they usually need some adaptation for personal requirements and, therefore, I couldn't get one to work that took the whole row as well.

Many thanks. Any help is very much appreciated
% PurrSent

 

[FracinDean]

Are you saying that each of the four row groups will be sorted separately, or that you want rows to pass back and forth between the four groups but skip some rows in the middle?

 

[PurrSent]

Hi FracinDean

Sorry not to have made myself clear Each of the 4 ranges are separate but a Macro for the Range rows 24:217 would suffice. I'm happy enough to change the ranges as required.

So, to further clarify, no passing back and forth etc

Many thanks
%

 

[FracinDean]

It sounds like you just want a pretty basic sort which you will repeat for 4 different ranges. If you know how to use macro recorder, you will get pretty close from that, then you can just modify the code. Try it. After you modify the code if it's not working, give us your code and the error message and we can help.

 

[PurrSent]

It sounds like you just want a pretty basic sort which you will repeat for 4 different ranges. If you know how to use macro recorder, you will get pretty close from that, then you can just modify the code. Try it. After you modify the code if it's not working, give us your code and the error message and we can help.

Thanks
That sounds a sensible idea - should have thought of that myself - doh
I'll give it a go and will post back ...
%

 

[PurrSent]

OK, I've recorded a Macro to Sort Column 'C'

It HAS sorted the column required, ie 'C', within the specified range ("B24:AB100")

BUT, it has also thrown up some rows at the top of the section that would be preferred to placed just below the 'sorted' rows, ie a '0', 2 x date & 'x' (I'm not sure where the '0' came from )

I notice these rows, except one row, have a date in 'B' although no name in 'C' - is this relevant, perhaps?

There is also a formula in cells C24:C28 [ =IF(Feb!C92=0,"",Feb!C92) ] bringing the data forward from the previous sheet, ie February - is this relevant, although other 'empty' rows have not been included in the 'sort' (as desired)

This is an image showing the first few rows/columns after the 'Sort'

This is the VBA created using Record Macro -

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

I do hope this VBA can be amended to prevent the issues mentioned above. As ever, I very much appreciate any help.
Thanks
%

Editted, to add the Range in the Macro has less rows than will actually be included. The eventual Range will be B24:AF218

 

[FracinDean]

This thread is taking a turn down a different path. The issues you are describing are because you have some invalid information in some of the rows you are trying to sort. IMHO you need to clean up your data and then the sorts will work like you wan.

When you use the macro recorder, you will want to clean up a few thing that you don't need in the code. One of the thins you can do is get rid of statements that aren't needed. For example, if you are going to have four sorts, you only need one ActiveSheet.Unprotect at the beginning of the first sort, and one ActiveSheet.Protect at the end of the last sort. You don't need the two .SELECT and one .SmallScroll method statements at the beginning of the code block, and you don't need the Sheets("Mar").Select statement at the end.

Good luck.

 

[PurrSent]

Hi FracinDean

Thanks very much for your explanation regarding the 'rogue' entries at the top of the 'sorted' list, and re the excess statements in the code. I'll work on removing the excess bits

Regarding cleaning up the data, there is a reason for the 'x' entries but I was hoping they might 'sort' to the bottom of the sorted list. Not to worry, that's how it is so I'll accept that. The '0' is definitely rogue and unlikely to appear again. If it does, it can get deleted

Thanks very much for all your time and effort in this. I really appreciate it. I'll not be able to work on it until tomorrow/next week but, unless I post back, please accept that all is working well, thanks to you.

Again, many thanks
%

 

[FracinDean]

You could probably define a custom sort order so that datessort ahead of anything that’s not a date, but I’ve never done anything with a custom sort thatis somehow recognizing the data type. Excel does have a cell contents property that can be used but I’m not sure about integrating it with a sort. I would not be the one that could do it at least not yet, still learning every day

 

[PurrSent]

You could probably define a custom sort order so that datessort ahead of anything that’s not a date, but I’ve never done anything with a custom sort thatis somehow recognizing the data type. Excel does have a cell contents property that can be used but I’m not sure about integrating it with a sort. I would not be the one that could do it at least not yet, still learning every day

Yes, certainly, that would be ideal to include Custom coding to achieve dealing with the 'odd' dates/characters but, as you can surely appreciate, that's way beyond my ability.

Thanks very much again, FracinDean. As I have already said, I really very much appreciate you spending time, and for your interest, helping me with this.

So, ... I need someone to write the necessary code for me!!! Would anyone like the challenge? It would be fantastic if someone could pick this up

%