+ Reply to Thread
Results 1 to 6 of 6

Only keep non-blank rows

  1. #1
    Registered User
    Join Date
    03-17-2016
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    25

    Only keep non-blank rows

    Hello

    Sheet 1 and sheet 2 are attached.

    Sheet 1 contains data with blank rows in between non-blank rows
    Sheet 2 contains the same data without the blank rows.

    I would like to automate sheet 2 with formula's such that I don't need to copy paste the non-blank rows from sheet 1 into sheet 2.
    Is there a way that I can do this using formulae?

    Appreciate the help.

    Regards
    Bruno
    Attached Files Attached Files
    Last edited by brunojacko; 01-11-2021 at 06:56 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Only keep non-blank rows

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn



  3. #3
    Registered User
    Join Date
    03-17-2016
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    25

    Re: Only keep non-blank rows

    Thanks for your reply.
    I have replaced the pictures with a small sample sheet.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,700

    Re: Only keep non-blank rows

    Put this formula in I2 of Sheet1:

    =IF(COUNTIF(A2:H2,"?*"),MAX(I$1:I1)+1,"-")

    Copy this down as far as you need to in order to include all your data - the hyphens will indicate where the formula is active.

    Then in A2 of Sheet2 you can use this formula:

    =IF(ROWS($1:1)>MAX(Sheet1!$I:$I),"",INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$I:$I,0)))

    Copy this across into H2, then copy all those formulae down as far as you need to (until you start to get blanks).

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    03-17-2016
    Location
    belgium
    MS-Off Ver
    2013
    Posts
    25

    Re: Only keep non-blank rows

    Thanks a lot, Pete_UK

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,700

    Re: Only keep non-blank rows

    You're welcome.

    I forgot to mention that you will probably have to use semicolons ( ; ) instead of commas ( , ) in the formulae, but presumably you sussed that out yourself.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Delete blank rows between blocks of data except two blank rows
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-26-2018, 11:18 AM
  2. Replies: 11
    Last Post: 07-30-2015, 12:35 PM
  3. [SOLVED] insert blank rows every nth row and copy down data to fill blank rows
    By surpass in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-06-2014, 02:55 PM
  4. Replies: 2
    Last Post: 09-18-2014, 12:20 PM
  5. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  6. [SOLVED] Delete blank rows after filter - if no blank rows exit sub
    By cmb80 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2012, 10:00 AM
  7. blank rows:program that deletes blank rows
    By phil2006 in forum Excel General
    Replies: 2
    Last Post: 06-19-2006, 11:10 AM

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