CODEWITHSUNDEEP
sqlexcelms-accessvba
littlerauws
littlerauws

Reputation: 11

Trying to use VBA to delete an entire row if one field in null/blank

So I am working on a project where I import an excel file into Access, but when I do the importing( the tables in excel have the same tables headings as in Access) I tend to get a bunch of extra rows because in my excel file I have functions behind most of the cells, so that is why, even tho it appears to be empty, Access transfers the rows even tho nothing is actually entered into it.

So my question is, is there a way by using VBA in Access that I can automatically, once the excel file is imported, It could loop through all the rows in a specific table and delete an entire row based on specific criteria. Like if within a row there is an empty field, it will delete the whole entire row. This will save me a lot of time, instead of manually searching through the table for blank fields and deleting the row myself.

I do have knowledge working with VBA but im unsure of how to go about doing this, I was trying to use a DELETE SQL statement, but couldn't figure out how to do it properly since I need it to be In VBA.

Upvotes: 0

Views: 2164

Answers (1)

APrough
APrough

Reputation: 2701

  1. Create a SQL query that pulls back the rows you want to delete.
  2. Once the query is finished to your liking, go into SQL View for the query, and remove everything before the FROM clause.
  3. Instead, type a DELETE there. Save the query. So it should look like "DELETE FROM blah WHERE blah, blah, blah"

So now you have a query that will delete rows that you want it to. Now, to get it to run through VBA (note that you can run it manually just by double-clicking it in the Queries pane), just put the following line in where you want it to run.

docmd.OpenQuery "yourDeleteQueryName"

Note that yourDeleteQueryName should be replaced with your Delete query name.

Upvotes: 1

Related Questions