Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Removing blank rows (XP)

    Maybe this belongs in the Access forum, but I'll start here since the process begins with Excel.

    I have a fairly large spreadsheet (40,000-plus rows) that I want to import into an Access database. The spreadsheet lists all the courses that all our students (employees, really) have completed since 1 January 2000. We exported this data from the application that originally contained it, and it's now in the form of an Excel spreadsheet. The spreadsheet is sorted by course, and it seems the application inserted a blank row between courses. The blank rows help the human eye see the break between courses, but two questions come to mind:
    1. <LI>Will these blank rows create any problems whatsoever when I import the spreadsheet into Access?
      <LI>If the answer to the first question is Yes, is there a quick and clever way to get rid of the blank rows?
    These questions occurred to me as I was slogging my way through this sheet and manually deleting the blank rows. There's gotta be an easier way! I thought about trying Search and Replace, but I don't know the Secret Squirrel way to search for a blank row.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing blank rows (XP)

    1. Yes, empty rows will be a problem in Access.

    2. If you select the entire range in the worksheet from the row containing the headers (field names) to the last filled row, and sort on one of the columns, that is filled except for the blank rows, the blank rows will end up below the filled rows. Access should ignore them when importing the spreadsheet, because they are at the end.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Removing blank rows (XP)

    you can use auto filter
    add a new column at the end that has in row2 the formula:
    =counta(A2:Z2)
    [change as desired for the columns to examine] then copy it down the rows
    Use data - filter - autofilter, select the pulldown from this column and chose the "0"
    Now only the blank rows are "visible".
    Select all the rows and delete

    Conversely, if you want to keep the blank rows, select custom in dropdown and make it "<>0" and you will have a copy without the blanks that you could copy this range to a new sheet and import that into access.

    Steve
    Then filter

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Removing blank rows (XP)

    Re: 1

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing blank rows (XP)

    Hans said:
    ========================
    2. If you select the entire range in the worksheet from the row containing the headers (field names) to the last filled row, and sort on one of the columns, that is filled except for the blank rows, the blank rows will end up below the filled rows. Access should ignore them when importing the spreadsheet, because they are at the end.
    =========================

    Yes but, perhaps you must clean your data base in Excel, before passing it to Access.

    Excel keeps which was the last used registry although this does not contains information and at the time of import the data to Access, will take the registries in blank.

    This routines will clean your sheets, chooses one and run it before to import the data base.

    This is only for the active sheet

Posting Permissions

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