Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Checking for Subtotals (XL2k SP3)

    I have written some code that removes a bunch of data, then sorts it, and the adds subtotals (from the menu; Data | Subtotals). In order to keep someone from running the code all over again; I want the code (macro) to first check to see if the subtotals have been turned on. Can I do that programmatically? Basically I want to do something like...
    If Selection.Subtotals = True Then Exit Sub (this doesn't work by the way)

    Can anyone point me in the right direction?

    Thanx

    Dennis

    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking for Subtotals (XL2k SP3)

    See the thread starting at <post#=448522>post 448522</post#>. Make sure to read the entire thread.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Checking for Subtotals (XL2k SP3)

    This is a very raw way of doing it, but run a find command to find the word "Subtotal" in the column that should contain the subtotals. Assign the find to a boolean variable. If the find command returns as true, then exit the sub.

    Example code:
    <pre>Option Explicit
    Sub TestForST()
    Dim ST_On As Boolean
    Columns("A:A").Select
    On Error GoTo BM
    ST_On = Selection.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    On Error GoTo 0
    BM:
    If ST_On = False Then
    Exit Sub
    Else
    MsgBox "Continue with macro"
    End If
    End Sub
    </pre>

    Regards,
    Rudi

Posting Permissions

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