VBA XL2003: How to sort with large header row of 4 rows.

[Mitchell]

Good Morning!

I was hoping someone could help with some older vba coding. A new spreadsheet desperately needs to be adjusted but I only have the sort code when I removed the header rows in a test sheet so that I could at least get that much to post here. Sorting 2 different ways but column B (Name) then C (Department) and then by column C (Department) followed by B (Name) but I don't know how to accommodate the header rows in the sort so that these aren't sorted into the data. Here is the preliminary code for both:

****************
Sub SortByName_NoHeaderRow()
'
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range( _
"D1"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("C1").Select
End Sub
****************
Sub SortByDepart_NoHeaderRow()
'
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range( _
"C1"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("D1").Select
End Sub
****************

The header rows cover 4 rows (or, from 1-4, ::, naturally).

Thank you so much, if anyone can help. And this has to remain in XL2003 code, unfortunately. Cheers.

 

[Kaper]

If there would be just one header row then easy

Header:=xlYes

instead of

Header:=xlGuess

would do

But as I understand, you have 4 header rows here, so try (assuming that before sorting you have selected whole table with all 4 header rows *):

Sub SortByName_NoHeaderRow()
'
    selection.offset(4,0).resize(selection.rows.count-4,selection.columns.count).select 'select all but header rows
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Key2:=Range( _
        "D5"), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal 'sort no headers, but keys in row 5th
    Range("C1").Select
End Sub

*) If not - do it

 

[Mitchell]

Drat, no. XL2010 didn't like it. I brought the file to work as I do have XL2010 at this particular job but must keep in XLS as all the other instances of use occur where there is only the older versions. I get a 1004 error here at work and it highlighted the offset code row that you added.

It was weird because I had a funny feeling XL wouldn't like it as I've never, ever seen anything so easy as just plunking in something as easy as an offset code. Granted, I don't recall ever having this exact situation before, but still. It's never that easy in Excel, no matter how great Excel is ::g::.

But I'll try it again at home, where it counts, tonight where I have one of the user instances involved which perforce has XL2003. (I'm not holding out much hope that it'll work there, either, but will test there, too).

p.s., I never manually pre-select an area to sort. That makes no sense to me when we're looking for a vb solution to something. So I don't know if that was a prerequesite to using this code. But, nah, if we're looking for a vb solution, stands to reason it should all be vb with no further user intervention.

 

[Kaper]

Well this forum do not allow to attach files, but if the code is not working in XL2010 I'd except the reason is:

assuming that before sorting you have selected whole table with all 4 header rows
...
If not - do it

See and test attachment from www.klubexcela.pl/forum/other-forums/4-vba-xl2003-how-to-sort-with-large-header-row-of-4-rows

 

[Mitchell]

Well this forum do not allow to attach files, but if the code is not working in XL2010 I'd except the reason is:

See and test attachment from www.klubexcela.pl/forum/other-forums/4-vba-xl2003-how-to-sort-with-large-header-row-of-4-rows

Oh, thank you for your time and effort. I really appreciate it.

The thing is that I have found that although I'm really dumb at this sort of thing even after years of trying (everything else I've eventually figured out, other scripting languages in all sorts of other programs, but MS vb just continues to elude me when I'm starting out with new spreadsheet needs ... !), that I can recognize what may work and what may not work. This code is clunky, though, in that it has to select the area first. I just know that Excel must have a more elegant way of doing this particular type of things without adding a potentially problematic physical step such as that. What it might be I don't know but I have some really complex code that Dave Peterson and so many MVPs over at the Excel vb ngs helped out with over the years so I know that a simple answer is out there that doesn't add this type of iffy element, it's just a question of how and whether or not dumb-dumbs like me in vb will ever find it <lol>.

Honestly, I don't know why I have so much trouble with new code ... I have hundreds of spreadsheets and code that I recycle and re-use all the time that I need absolutely no help with, I've learned how to work with them ... but trying to figure variations out on my own?? Disastrous.

My sheet has the added complication that the name column starts at C5 and the department one at D5. Without selecting, without identifying a range and naming a specific sheet, how on earth am I going to get this to work??? No problems without a header row; all my spreadsheets work no matter what the sheet is called and even if many rows are added - because nothing is set in concrete in terms of rangers, sheet names, etc. Further, the test that I managed to get working within the current parameters knocked out the relationship between name and department! So I got great results, with some code I found later via googling, and the names sorted out just fine without anything complicated or iffy in the code but the people then got listed in the wrong department ... so I've tossed that example completely out the window! <sigh> Sheesh ...

So I'm going to continue googling. So hard to find code sometimes. But hopefully the answer will be out there somewhere.

Thanks once again for everyone's time. We'd be nowhere without you folks <g>. Cheers.

 

[Mitchell]

Good Morning,

All the sorting codes that I have in various spreadsheets that date years (that deal with only one header row, though), sort without being forced to pre-select the data even programatically.

I felt that I had to come back and explain why I've never liked it as I am only a power user and I'm not all that great at vb but I have years of working with Excel under my belt and have ru into problems - and pre-selecting data is just asking for trouble. In the early days, I was helped out with code that worked that way. I remember that sheet well as it was one I used for logging that I accessed frequently during the day and every day. That type of sheet really highlights issues and this one showed that over time, you're going to run into something that glitches your system and messes up your sheet. Something eventually always comes along that provides a hiccup right when your macro is pre-selecting - a process starts up, the PC may freeze momentarily and that selection part gets interrupted and then your data goes haywire - relationships get skewed, your rows or columns go every which way no matter how minor ... something always goes wrong. And, btw, you're out of luck because you can't come back from vb-generated events with ^z. That recovery keystroke only seems to work when you manually make an error. So what happens is you have to exit your document, losing a little or a lot of work when you do that, just so you can recover your sheet to a previous correct state.

So I have avoided those types of steps in my Office documents since then.

_All_ the sorting code in spreadsheets that I have, some years old - and that I _still_ use today because I don't fix what isn't broken - have never had a need for pre-selecting. Granted, they've only been 1-row header rows but surely the same would apply to 4-row headers rows? I have some sheets that do some pretty complex, complicated and incredible things (when I needed a database but had no choice but to accomplish the task in Excel), so I know that it won't be limited to this one option of dealing with multi-row headers. Excel is just too powerful to have such a weakness as that.

So here's hoping that someone knows a solution that works to deal with vb sorting of a sheet that needs relative vs absolute referencing (i.e., no sheet naming (i.e., activesheet vs sheet1 - we often have to change those sheet names), no ranges name (these can change as we add data), and just by dealing with column C and D within the print range as we may end up adding rows and rows of information as our sheet grows. Every time I've had a limitation imposed by something being named outright, that has affected the quality of the sheet as it eventually always stops working. And going in to manually fix the ranges, etc.??? Um, no. Not elegant at all ... <g> Relative referencing has always been the best way to go.

Thank you!