+ Reply to Thread
Results 1 to 10 of 10

Insert and Delete blanks rows so that there are 2 blanks rows between text rows

  1. #1
    Registered User
    Join Date
    06-28-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    Excel 2010: Need a Macro. I am not a coder and need desperate help. I have a several workbooks with thousands of blank rows with text in between. Some of the blank rows maybe one line 2 or more. I would like it more consistent. I would like to have 2 blanks rows between any text rows in an entire Worksheet. Some of the rows may have 1, 2, 3, or 4 rows of text. I'd like to keep that data the way it is. So if three rows of data are together they stay together. Only affect the blank rows.

    For example:

    *Data Rows---*
    *Blank Row*
    *Data Rows---*
    *Data Rows---*
    *Blank Row*
    *Blank Row*
    *Blank Row*
    *Blank Row*
    *Blank Row*
    *Data Rows---*
    *Data Rows---*
    *Data Rows---*
    *Data Rows---*
    *Blank Row*
    *Blank Row*
    *Blank Row*
    *Data Rows---*
    *Data Rows---*


    into this:

    *Data Rows---*
    *Blank Row*
    *Blank Row*
    *Data Rows---*
    *Data Rows---*
    *Blank Row*
    *Blank Row*
    *Data Rows---*
    *Data Rows---*
    *Data Rows---*
    *Data Rows---*
    *Blank Row*
    *Blank Row*
    *Data Rows---*
    *Data Rows---*

    Can someone please help???

    Thanks.
    -Ernie
    Last edited by erniedawg; 06-28-2013 at 02:42 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    Try this...

    Select the range >> F5 >> Blanks >> delete rows

    Now that all the blank rows are gone, number the range from 1 to n in the column next to the range with data

    Copy the 1 to n and then paste it below the last number >> go to the last number again and paste

    Now sort the number smallest to largest then delete the rows with the numbers
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    06-28-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    I got as far as deleting the blank rows but then you lost me. I do not want to sort the data. I'd like to keep it in order. It should be easier with a macro?

  4. #4
    Registered User
    Join Date
    06-28-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    How about 2 macros?

    One macro to delete any adjacent blanks rows more than 2.

    Then a 2nd macro to insert ONE blank row to any single blank rows.

    This will make all data rows to have 2 blanks rows in between.

    Can anyone help me with this please?
    -Ernie

    Quote Originally Posted by jeffreybrown View Post
    Try this...

    Select the range >> F5 >> Blanks >> delete rows

    Now that all the blank rows are gone, number the range from 1 to n in the column next to the range with data

    Copy the 1 to n and then paste it below the last number >> go to the last number again and paste

    Now sort the number smallest to largest then delete the rows with the numbers

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    Try...

    Please Login or Register  to view this content.
    Note: Please do not quote full posts unless necessary for clarification, otherwise it just creates clutter.

  6. #6
    Registered User
    Join Date
    06-28-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    That only deleted all the blank rows. I need two blank rows in between all text.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    Try this sample
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    Ernie,

    Supposing you mean entire rows blank, and not just blank cells in columnA, try this
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-28-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    Kalak, thank you! That was exactly what I was looking for. It worked perfect.

    Jeff, thank you also for your help.

    I know I may have not been 100% clear. It's rather diffcult to explain. Thanks again!!

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Insert and Delete blanks rows so that there are 2 blanks rows between text rows

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1