Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sorting by Column A and adding sub totals

    On my worksheet, Column A contains one of 3 codes: m, p, or r.

    Currency values are then entered into either Column J, L, N, O, or P, depending on how I pay for the item, and which code appears in Column A, (I use 5 methods of payment - - 3 credit cards, check, or cash, hence the J, L, N, O or P)

    I would like a macro - triggered by an Alt-key or Cont-Alt-Key that would first sort all the data by Column A by code (m, p, or r - this part is easy) - but one that would also add several spaces and a labeled subtotal after the sorting/grouping of m's, p's, and r's.

    Thanks, Bill Vitez
    Last edited by Bill Vitez; 2011-11-03 at 10:28. Reason: grammatical errors

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    Here's some macros that meet your requirements, with the exception of the blank lines. BTW, this is fairly easy to do manually with the Sort and SubTotal functions on the Data Menu {Excel 2003}.
    Code:
    Option Explicit
    
    Sub SrtAndSubTotal()
    
    
        Range("Database").Select
        Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
            
        [A2].Select
        
        Selection.Subtotal GroupBy:=1, Function:=xlSum, _
          TotalList:=Array(13, 14, 15, 16, 17), _
          Replace:=True, PageBreaks:=False, SummaryBelowData:=True
            
    End Sub   '*** SrtAndSubTotal ***
    
    Sub RemoveSubTotals()
    
        Range("A2").Select   '*** Make sure only one Cell is selected ***
        Selection.RemoveSubtotal
        
    End Sub   '*** RemoveSubTotals() ***
    I've attached a test workbook with the CTRL+Shift+S running the SrtAndSubTotal code and CTRL+Shift+R running the RemoveSubTotals code. Note I hid all the intermediary columns. You will also need to define your used area as Database to use the code unaltered {you can see this in the sample workbook by pressing F5 the selecting Database and it will highlight the area. There are other ways to select the data area but not having a sample of your workbook I couldn't decide on the best way so I chose the way I knew would work.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Bill Vitez (2011-11-03)

  4. #3
    New Lounger
    Join Date
    Nov 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you! With the exception of adding blank spaces after each sub total, the macro you sent does exactly what I need.

    BV

  5. #4
    New Lounger
    Join Date
    Nov 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The macro supplied by "RetiredGeek" works well in the worksheet he supplied, but I cannot get it to work in mine. So I have attempted to attach a portion of my actual worksheet which is entitled Med Expenses - hopefully "RetiredGeek" or someone equally as proficient in Excel as he can accomplish what I asked for in my earlier post. (Sorting by Column A and Adding Subtotals - - posted on 11/3/2011)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    When I opened your copy of the workbook I noticed the conversion box which means you are using a version newer than 2003. In both 2007 and 2010 you need to save the file as a .xlsm {excel macro} file for the macros to work properly. It also helps if you define the directory as a Trusted Location in the Security & Trust Center. If these things don't fix the problem please post back.

    Update: After looking at your sheet I see you have blank columns that your original description didn't mention. Attached is your worksheet with minor adjustments to the code to take your formatting into account.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2011-11-03 at 20:21.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    New Lounger
    Join Date
    Nov 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I am sorry for my two mistakes - I meant to mention that I am using Excel 2011, and I was not explicit enough when I gave column headings as I neglected to mention the blank spacer columns. I thought the column headings I gave would indicate blank/unused columns were between them.

    If it is important, the worksheet in which I want the macro to run is called "Med Expenses", and it is part of a larger workbook entitled "Copy This One Experimental 2011 Medical and Credit Card Bills". I tried editing the macro you sent to see if including the proper title for the range in the macro would make a difference but it didn't. As before, the macro works well in the sample I sent to you but no where else.

    FINALLY - when I copy the macro onto the full worksheet and attempt to run it I get an error message - a red X and the number 400

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    The Range in the macro is not the name of the worksheet, it is just the area being used. If you hit F5 on the latest sample then select Database you'll see the area highlighted just encompasses the used area. What you need to do in your actual workbook is to select the used area and Name it Database by typing Database in the NAME box and pressing the Enter key, then the macro should work just fine. Just remember to insert new rows between existing ones which will automatically adjust the Database range. Since the macro sorts the data anyway this won't make a difference.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    New Lounger
    Join Date
    Nov 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Gads, I keep making mistakes perhaps I have been at this thing too long. I use Excel 2010 not 2011. I tried what you said and still get the red X 400 error message.

    Anyway I have attached the entire file which I would like sorted by column A and then subtotaled by m,p, and r.

    If you'd like to try and if you get it working for the sheet I have sent, then perhaps I can simply move the entire sheet, macro and all, into the bigger workbook.

    This will be my last try - I won't bother you anymore.
    Attached Files Attached Files

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    Attached is your workbook with the macros installed and running in Excel 2007 which is the closest I have to your 2010 version. I've done the following:
    1. Set the Sort & Sub-Total code to run via Ctrl+Shift+S
    2. Set the Remove Sub-Total code to run via Ctrl+Shift+R
    3. Created a Dynamic range name "Database" that will automatically adjust to new entries added on the end of the list, as long as you don't exceed 30,000 entries.
    4. Placed the Header "Code" at the top of Col A and deleted the x's you had there they are un-necessary and the header makes things easier to read and understand.
    5. Tested the whole thing and it works.

    Sorry but your plan of moving things to a bigger workbook won't work since the Macros {VBA Code} are not attached to a given sheet. You can however move sheets from another workbook into this one.

    If you need to move this into another workbook you'll have to:
    1. Export Module 1 to a .BAS file then import it into the other workbook.
    2. Re-Establish the shortcut keys via Tools, Macros, Options {or the 2010 equivalent}.
    3. Re-Establish the Dynamic Range name "Database" as: =offset(Sheet1!$A$3,0,0,CountA(Sheet1!$A$3:A$A3000 0),18)

    I hope this works for you.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #10
    New Lounger
    Join Date
    Nov 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The new macro works beautifully in the spreadsheet you sent back to me - however there is a glitch -at least for me. In order to move things into another workbook I can do what you say in directions (1) and (2) - but direction (3) is giving me fits. Is it possible that you could give me a more explicit way to accomplish step (3)? (i.e., Tell me exactly what I have to do). Whatever I have tried is obviously wrong because when I try to run things all I get is a beep - and when I debug I keep getting syntax errors and I don't know much about VBA so I am at a loss!

    1. Export Module 1 to a .BAS file then import it into the other workbook.
    2.Re-Establish the shortcut keys via Tools, Macros, Options {or the 2010 equivalent}.
    3.Re-Establish the Dynamic Range name "Database" as: =offset(Sheet1!$A$3,0,0,CountA(Sheet1!$A$3:A$A3000 0),18)

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,
    1. On the Ribbon select the Formulas tab.
    2. Select the Define Name drop down.
    3. Click Define Name...
    4. In the Name box: type: Database
    5. In the Refers to: box type or copy the formula from previous post.
    6. Click OK.

    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    New Lounger
    Join Date
    Nov 2011
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Okay I give up - BUT I do thank you for all your efforts on my behalf. I did everything you told me to do in your last post. At first I got several syntax errors in the compiler, but I got them fixed so that the macro would compile properly - now when I try to run the macro (cont-shift-s) I get a different error - error #1004. I can do what I want done manually anyway - I just thought a macro do do it all in a keystroke or two would be super. Thank you again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •