procedure exceeds 64K

[justme1052]

After getting help from you guys on how to do the macros auto sorting, I have played around with it a couple of day to make sure I understood what to do to make it work for me. So far all was good. so yesterday I spent most of the day doing my entire program.
Only problem was when I finished it I got an error saying: Code for this procedure exceeds 64K when compiled.
Break this, and any other large procedures, into two or more smaller procedures. then it gave me the entire code thing, I think explaining what cell I started with and what cell I finished at, and a whole bunch of stuff in between that I have no idea what they mean. but I did copy it just in case I may need it.

My question is when using Macros record Now and you get a message like this -procedure exceeds 64K Break this and any other large procedures, into two or more smaller procedures. is there a way to insert something into the code thing it was showing me to break it into section that will allow it to run my program. can I like put part 1 and part 2 in the code thing without having to do the whole program over again. man I hope so.

[TMS]

If you have used the macro recorder to create your code, it records every cell selection and screen movement, most of which you don't need.

Bear in mind the code you get from the macro recorder will repeat exactly what you did ... warts and all!

First thing to do is get rid of all the screen movement instructions ... like small scroll, in fact, anything to do with scrolling.

In the meantime, post the code ... or, even better, a sample workbook, and you will get more detailed advice.


Regards, TMS

[codeslizer]

Hey buddy,

Basically when you record macro, Excel records every-single-tiny settings from the sheet. For example, if you'd just like to bring the text to the center both vertical and horizontal ways, Macro would be :

Sub Macro8()
'
' Macro8 Macro
'

'
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

whereas you needed to make change in just two settings - Vertical Alignment and HorizontalAlignment. Following code can replace the above code.

Sub Macro8()
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
End Sub

Hope, you're getting me. Record Macro is an AWESOME tool which can be used as a mentor for oneself. Its helps us get an idea of how things work. After learning, one should tweak the codes and filter the unnecessary codes out of your procedure (method or macro). This is to ensure we don't overload our processor with unnecessary instructions so that we get fast and perfect solutions for us.

[justme1052]

Back to TMSHUCKS and codeslizer, please beleive me when I say I'm slow, but when I catch on I'm ok. TM ask for a sample work sheet and the code I got,

here a sample of what I'm trying to do. The columns are in two C-D E-F G-H I-J and so on. The rows here are 7-14 as you see I have to sort some high and some low. Is this possible to do a auto sort for each set of columns

C---- D----------E------F--------G------H--------I------J
BEST-------------AVG-------------EARLY-----------LATE
SPEED-----------SPEED------------SPEED---------- SPEED
BOX--LOW-------BOX--LOW------BOX----HIGH------BOX--HIGH
1---- 31.3-------1---- 31.47 ----- 1-----87.55------1-------0
2---- 31.2-------2-----31.69------2---- 51.53------2-----(-1
3---- 31.85------ 3-----31.95------3-------0--------3-------0
4---- 31.36------4-----31.83------4-----45.53------4-----(-2
5---- 31.4-------5-----31.91 ------5-----17.5-------5-------6
6---- 31.4-------6-----31.84 ------6-----38.52------6-------1
7---- 31.28------7-----31.47 ------7-----100--------7-------1
8---- 31.03 ------8-----31.46------8----94.55-------8-----(-1

remember the workbook is in set of 2 wit 10 set or 20 columns and 10 rows per column with 15 races a night, so it is pretty long.
Hera a copy of the code error:
Code for this procedure exceeds 64K when compiled.
Break this, and any other large procedures, into two or more smaller procedures.
---------------------------------------------
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AB160").Select
ActiveWindow.SmallScroll Down:=114
Range("Y262:Z270").Select
ActiveWorkbook.Worksheets("Mor Setup").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Mor Setup").Sort.SortFields.Add Key:=Range( _
"Z263:Z270"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Mor Setup").Sort
.SetRange Range("Y262:Z270")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AC273").Select
ActiveWindow.SmallScroll Down:=138
End Sub

I hope this helps and is what your asking for.

[TMS]

@justme1052: I'm afraid that the information, as presented, is not very helpful. What would be more useful is a sample workbook, or the actual workbook if it does not contain any sensitive information. The code should be in the workbook.

Please also note that, to comply with forum rules, you should also add Code Tags around your code above.

In the meantime:

.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AB160").Select                       ' redundant because superseded below
ActiveWindow.SmallScroll Down:=114   ' get rid of anything like this (with scroll in it)
Range("Y262:Z270").Select
ActiveWorkbook.Worksheets("Mor Setup").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Mor Setup").Sort.SortFields.Add Key:=Range( _
"Z263:Z270"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Mor Setup").Sort
.SetRange Range("Y262:Z270")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AC273").Select
ActiveWindow.SmallScroll Down:=138                    ' again, get rid of anything like this
End Sub

Regards, TMS

[justme1052]

Please before I go any further,please tell me how to get a code tag. I do not want to be no longer allow on this sight. this sight is so helpful. A couple days ago two guys name FDibbins and mike65535 spent a few hours with me talking and walking me step by step to learn how to create a macros. but with all their help, FDibbins had to click problem solved because I could not find the icon he was talking about or the star to thank them then and I still do not know where they are at. so let me go back and again read the rules. I'm not trying to use my age as a excuse but when it come to the computer stuff I do not understand all the words when asks to do this or that. but to keep from getting kicked out let me go see if I can find a code tag and a problem solved and thank you star icon. please bare with me and I'm sorry if I seem so lost, but I really am. So before you try to help me with my problem let me find the code tag and icons I need to know to follow the rules. Even when I'm not asking for help I enjoy reading the forum and learning from it I still want to come to this site, but I will learn the rules. Thank for that advice.

[TMS]

Don't worry, I'm sure there are no plans to ban you.

If you click on the edit option on the bottom right of the box where you see your text, it will open the window for you to make changes.

If you select/highlight the code, then click on the hash (#) icon, it will put the code tags in for you. Then save your amended post.

Regards, TMS