Insert blank row after data change

Posted on 2010-01-08
Last Modified: 2012-05-08
I have a spreadsheet that I do frequent sorting on it, however, I would like to be able to automatically insert a blank row after a specific data change (say column E0. Then the next time when I sort by another criteria, I can then insert blank rows again (this time after data change in column D).

Thank you in advance.
Question by:squirrelzan
    LVL 39

    Expert Comment

    That's not very clear to me, can you precise or paraphrase?


    Author Comment

    I have the following spreadsheet

    Part #      Qty
    Box 1      10
    Box 1      20
    Box 2     10
    Box 2     50

    I need it to add a line after each different Part #. Unfortunately, we are talking about 1000 lines, so if there is a way for me to do it (not manually), it would be great.
    LVL 39

    Accepted Solution

    try this, assuming your part #s are already sorted


    sub InsertRows()
    dim lstRow as long, i as long
    lstRow=range("A" & rows.count).end(xlup).row
    for i=lstrow to 3 step -1
    if cells(i,"A")<>cells(i-1,"A") then _
    next i
    end sub

    Open in new window


    Author Closing Comment

    I guess, macro is the way to go.

    Thank you
    LVL 39

    Expert Comment

    THanks for the grade.

    If you don't want a macro, I'd put the following formula in a new columns starting in row 3
    copy it all the way down, do a filter on the true value, select the visible values
    Do Alt+;
    then Alt + I + R
    to insert rows about the selected values.



    Featured Post

    Free Trending Threat Insights Every Day

    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.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

    733 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

    23 Experts available now in Live!

    Get 1:1 Help Now