+ Reply to Thread
Results 1 to 14 of 14

Blank Rows on a Worksheet How Do I Eliminate

  1. #1
    Registered User
    Join Date
    04-01-2019
    Location
    Ohio, Fremont
    MS-Off Ver
    Office 365
    Posts
    38

    Blank Rows on a Worksheet How Do I Eliminate

    I have data from a Genealogy Database that I have exported to a CSV file that was then converted to a Excel Workbook (.xlsx).

    In this conversion process I end up with every even numbered row in the Excel Worksheet from Row 5 to Row 423 with no data or blank.

    I would like to eliminate these blank rows. I can do this manually by deleting every row but this is a rather time consuming process. Is there any way to automate this?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

    Re: Blank Rows on a Worksheet How Do I Eliminate

    If you import your .csv using PowerQuery, you can clean up the data during the import - that's what I'd do, filtering out the empty rows before loading to the sheet.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    206

    Re: Blank Rows on a Worksheet How Do I Eliminate

    or..another way...

    1. Select your whole worksheet by clicking the square to the left of column A and above row 1
    2. Press F5 key
    3. click 'special' (bottom left)
    4. select blanks
    5. finally...delete rows. (edit>delete)

  4. #4
    Registered User
    Join Date
    04-01-2019
    Location
    Ohio, Fremont
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Blank Rows on a Worksheet How Do I Eliminate

    Thanks for your response. I am not familiar with the use of a Power Query. I did search this on the internet and I could see where it could do what I want, but it would take some learning on my part.

    I decided to convert my information on my worksheet into a table and then did a sort and just eliminated the blank rows that way.

    I am going to continue to learn about the use of the Power Query. I was surprised to learn the basic book on Excel makes no mention of this type of query. The Power Query reminds me of some of the capabilities in Microsoft Access.

    Thanks again for your response, it triggered me to consider the use of a table.

  5. #5
    Registered User
    Join Date
    04-01-2019
    Location
    Ohio, Fremont
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Blank Rows on a Worksheet How Do I Eliminate

    I found this method on the internet on You Tube. This method works fine is there are no blanks in any of the columns, which is the case in my worksheet. You tube also shows some alternate methods to the F5 approach. I did not use these approached as I solved my problem by just converting my Excel Worksheet to a table and then did a sort and eliminated the blank rows this way.

    Thanks for suggesting this approach, but as I mentioned it didn't work for my particular worksheet.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

    Re: Blank Rows on a Worksheet How Do I Eliminate

    If you provide a .csv file, I can show you how to set up PowerQuery. Itís very easy.

  7. #7
    Registered User
    Join Date
    04-01-2019
    Location
    Ohio, Fremont
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Blank Rows on a Worksheet How Do I Eliminate

    Attached is basic example the CSV file I get when I export the report List of Individuals from Family Tree Maker 2019 software.

    My list of individuals had over 400 rows.

    This basic export is just a few rows but the steps to eliminate blank rows should be the same.

    I am not sure why the CSV export results in blank rows but it is a problem if you want to work with the data.

    If you could show me how to use a Power Query it would be appreciated.

    Thanks for your help.
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

    Re: Blank Rows on a Worksheet How Do I Eliminate

    OK - here's what you need to do:

    1. Go to the Data ribbon and select From Text/CSV.
    2. Find and select your CSV file and click Import.
    3. Click Transform Data at the bottom - this will load the data to the PQ editor.
    4. With Column1 highlighted green, use the filter drop-down at the top of it to untick blank and empty rows (two boxes).
    5. Click on the table icon to the left of the Column1 header and select Remove Top Rows - type 1 into the dialog box and OK.
    6. On the Transform ribbon, select Use Top Row as Headers.
    7. On the Home ribbon, select the Close & Load drop-down and choose Close & Load To - choose Existing Worksheet and then the cell where you want the table to appear - OK.

    Your cleaned data table will appear in Excel:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    Name Birth Marriage Death Spouse Name
    2
    Mouse, Minnie 07 Aug 1877 26 Nov 1893 7-Jun-47 Mouse, Mickey
    3
    Duck, Daisy Duck, Donald
    4
    Fudd, Elmer 28-Dec-14 24-Nov-40 6-Dec-12 Fudd, Elsa
    5
    Tarzan Jane
    6
    Anna 17-Jan-04 13-Nov-06 Kristoff
    7
    Kermit 01 May 1862 26 Nov 1893 21-Oct-19 Miss Piggy
    8
    Tink 08 Jun 1896 Terrence
    9
    Ariel 28-Apr-09 11-May-45 11-Oct-91 Eric
    10
    Simba 29 Oct 1894 1925 Nala
    Sheet: Sheet1

    The M Code generated is here:

    Please Login or Register  to view this content.
    You can duplicate this query at any time to import fresh data - just change the source location.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-01-2019
    Location
    Ohio, Fremont
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Blank Rows on a Worksheet How Do I Eliminate

    I followed your instructions and they worked fine with the sample data, however when I tried it on my actual data I was not given the options of empty rows.

    I have attached a pdf file of the screen shots I get for the two different files.

    The top screen shot is for the sample data I sent to you and the bottom screen shot is when I tried it with the actual data.

    Do you know why I am not getting the option of empty rows. I thought I followed the instructions the same way for both files sample data and actual data?
    Attached Files Attached Files

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

    Re: Blank Rows on a Worksheet How Do I Eliminate

    You have empty rows there, but no blank - that should be enough. You can't filter out what's not there! Should work fine.

  11. #11
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkey
    MS-Off Ver
    Office 2010 - 32 Bit
    Posts
    671

    Re: Blank Rows on a Worksheet How Do I Eliminate

    An alternative with VBA;

    Please Login or Register  to view this content.
    Last edited by Haluk; 07-04-2020 at 12:47 PM. Reason: code is revised...

  12. #12
    Registered User
    Join Date
    04-01-2019
    Location
    Ohio, Fremont
    MS-Off Ver
    Office 365
    Posts
    38

    Re: Blank Rows on a Worksheet How Do I Eliminate

    Thank you I tried again and it worked fine. I appreciate learning how to use Power Query for this task.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

    Re: Blank Rows on a Worksheet How Do I Eliminate

    No problem! Always glad to spread the word about PQ.

  14. #14
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkey
    MS-Off Ver
    Office 2010 - 32 Bit
    Posts
    671

    Re: Blank Rows on a Worksheet How Do I Eliminate

    Although the thread is marked as "SOLVED", my "revised" alternative in message #11 can also be used when PQ is unavailable.

+ 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] how can i eliminate blank rows that exist beyond the last non-bank row?
    By Joe Miller in forum Excel General
    Replies: 8
    Last Post: 06-03-2018, 06:09 PM
  2. Replies: 1
    Last Post: 02-23-2015, 09:42 PM
  3. SumIF and eliminate blank rows
    By jkktx81 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-06-2015, 07:34 PM
  4. [SOLVED] Eliminate blank rows when pulling criteria based data from one spreadsheet to another
    By Terisammis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-28-2014, 10:39 AM
  5. Replies: 7
    Last Post: 02-01-2014, 01:48 AM
  6. Eliminate blank rows by shifting rows up
    By jman0707 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-06-2008, 02:10 AM
  7. Replies: 2
    Last Post: 01-24-2006, 12:45 PM

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