Blank rows inserted after import of actual records

  • Hi all,


    I am using the import function in Access to import data from Excel to Access. I use the usual Get External File, choose the appropriate sheet, let Access assign the ID, and so on.


    My original records in Excel only have 43 rows including the column heading. The problem now is that the table created in Access somehow extends the list to ID 64999, so I have all these blank rows from row 43 to 64999.


    My table in Excel has no empty rows nor column, i.e. all of the relevant range in the sheet are filled with values. I have a few lists with many '0.0' values but I don't think that is the problem.


    Before the import, I actually have a macro which converted the data into this list format so that they can be imported in Access. I'm not sure if that might be the cause..?


    Can anyone enlighten me on how I can make all these blank rows disappear? I have done this importing before with other files and usually they work fine.

  • Re: Blank rows inserted after import of actual records


    Qwirky,


    There is something in the Excel file that is referencing a cell on the 64999th row. This could be your macro, a named range or a formula. Access is then seeing that there is 'data' on these rows.


    Open the workbook in Excel and open the VBE and in the debug window type


    Code
    ?activesheet.usedrange.address


    Does the address match the area you expected?


    A.

  • Re: Blank rows inserted after import of actual records


    Hi,


    Thanks for your help. You are right, there is some unseen data in one of the sheets. I found this out by deleting empty rows and columns from the sheet and the file size became drastically smaller.


    I think the macro that I used to get the data in list form creates this unseen data. I'm not sure if I should make changes to that macro or create a new macro to just delete empty rows and columns in the sheet. Appreciate it if I can get any help in either one.


    This is the macro that I currently have which after pasting the relevant data, then creates all those unseen data and makes the file size huge:




    Regarding the code that you give me, firstly I'm not very familiar with macro and I'm not sure where I'm supposed to insert it. Am I supposed to create a new sub? When I copied & pasted the line, the "?" sign became "Print" in Microsoft VB.


    Thanks for your help!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!