How to quickly remove subtotals to create blank rows between different items

Posted on 2014-08-28
Last Modified: 2014-09-15
hi Folks
If I use sub-totals on a list and then want to quickly remove the sub-totals so I'm just left with my sorted list with a blank row where my subtotals used to be...any suggestions on how to do that elegantly? Thanks.
Question by:agwalsh
    LVL 47

    Assisted Solution


    pls try

    Sub macro3()
    For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
        If c Like "*Total*" Then
        End If

    Open in new window

    LVL 31

    Accepted Solution

    Or you could do a Find & Replace:

    Find:   =SUBTOTAL(*

    Replace: leave blank

    Rob H

    Author Closing Comment

    Excellent solutions both. I've given RobH the best solution because it's easier for the user...that's the only reason.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now