Probably the easiest way to remove empty  rows is to sort. All of the
empty rows  will then be together and can be deleted easilly.

If you want to maintain the row  order though... First add a column and
fill it with 1,2,3,4,5,6,7,8,9,10........n.
Then sort the rows and delete the unwanted ones. Then sort again on
the column you put in. The extra column can then be deleted as well.

See, the thing is the excel  file is maintained in a different department, and i
dont have any authority to change it...........
so adding a column to sort wont be possible...............
i hope u understand what i mean...................
is there any way, where i can arite the code, and take care of
that.

IF you need to keep the records in a
certain order. You can then also delete out the rows. Just do not save
the changes you have made to the excel  spreadsheet, you can create a
copy of the sheet  to use to import  into Access. If you do not delete
the blank rows, then Access will stop importing  after it reaches the
first blank row. For instance, if you have 10 records, and then a blank
row, and then 300 more records, only the first 10 records will be
imported into Access.

As part of the import wizard features of Access, you have the ability to
indicate that your data has a header row  indicating the names of the
fields.

Thanks for the replies...........
See the thing is dont want to use the wizard.........
i want to do it programatically...........
What lisa said, i have been researching on it........... but for the sorting and
all, i have to do it manually before i can import  in to access........
which is not possible...........
so i was thinking some sort of code to be written which can do taht for
me........
Dawn thanks for telling me about blank rows, i never knew about that.......
but still i will request you guys to suggest something else, which will require
the least interaction and the import will be automated.........
Really thanks a lot for ur responses.........
i am still waiting for more guidance from the group..........

There are a number of ways I think but probably the best is to use ASP
to read the Excel file and write to your Access table ignoring blank
rows.

Another is to have an Export button in Excel to export a text file and
an import  button in Access to read it into a table and ignore blank
rows though that's only semi "automatic"... still better than running
the import wizard though :-) I gather that's what you're doing
now???

Do it manually with the recorder turned on. That will create a macro
for you to use for the next time.

Explore the TransferSpreadsheet feature of Access to help you with your
import. You can call all of it from Access once you have the manual
steps figured out.

'Run the macro in Excel
'You will need the Excel library referenced in Access
Dim xls as Excel.Application
xls.Application.Run("MacroName")

'Import the spreadsheet into the Employee's table
DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

Hopefully, this will get you going.

Thanks for the reply. I did that. These are the stps i followed.
1. Selected all the ranger to be sorted.
2. Sorted the excel  sheet so that the blank rows  come all the way on the end.
3. i recorded that macro and saved it in my personal folder.
4. the code saved is as follows.

Range("A2:E2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A2:E2992").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.SmallScroll Down:=-543
ActiveWindow.ScrollRow = 2
Range("A3").Select
ActiveWindow.SmallScroll Down:=-3

5. after gettign done i stopped the macro recorder.

now my question  is if the number of rows keep on increasing, this macro wont run
the rows more than what this macro recorded. So how do i need to take care of
it.

secondly, i was thinking, whenever the mail file is update  is there a way that i
can save a copy of it automatically  at a different location, and then import
from that location. I need to do this is because of permissions.

What I would do to ensure that you get what you want, is I would modify
your code just a little:

'Make sure you use whatever the name of the sheet  that you are going to
import
Worksheets("Sheet1").Activate
ActiveSheet.UsedRange.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

You don't need the scrolling movements, because they were recorded as
you were confirming that everything was selected and that it worked
before you turned off the recorder.

The ActiveSheet.UsedRange.Select makes sure that you include all cells
that have any values in them. Which should eliminate the problem that
you have with making sure you don't miss cells/rows that have been added
since the last time you run the code.

Yes, you can save the file with a new location and file name to use in
your import.

ChDir "C:\"
ActiveWorkbook.SaveAs Filename:= _
"C:\ToBeImported.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

There's been quite a bit of discussion on this, but back to the original post
for a slightly different slant ...

If you don't want the blank rows, why not simply eliminate them after importing
the table? You'll be able to do this with a single SQL statement. This leaves
full control at your end.

(I'm assuming that the blank lines don't actually impede the importing  of the
table - just show as records with null values in all fields.)

So u mean stripping out the nulls at the database
level. But as per Dawn, as soon as access  will see an blank row  in excel  file,
it will stop importing. So, i think that will be a problem. What do u think
about that. I will really appreciate that.

Yep, I suspected that might have been the case - hence my parenthesised comment
at the bottom of my message.

But I've just tried it and can handle empty  rows just fine. Environment is
Office 2003 on XP Pro.

I imported the Excel sheet  into Access as a table. I selected that the sheet
contained column headers. I got rows  from the first down to the last in my
sheet, with blank rows showing as blank records.

I also linked the same Excel sheet into Access as a linked table. Again, I
selected column headers. Again, I got rows from the first down to the last,
with blank rows showing as blank records.

I tried it with several blank rows together - specifically non-blanks in row  6
and row 45 with blanks in-between.

What method are you using to link the Excel into the Access?