+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 18

macro to delete entire rows if certain cells are empty

  1. #1
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    macro to delete entire rows if certain cells are empty

    Hi,

    I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.
    So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.

    I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.

    Please Login or Register  to view this content.
    I'm sure it's something simple... like me !

    Any help much appreciated

    Wrightie
    Last edited by wrightie; 03-05-2009 at 10:11 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,205

    Re: macro to delete entire rows if certain cells are empty

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: macro to delete entire rows if certain cells are empty

    How about this:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: macro to delete entire rows if certain cells are empty

    apologies for that royUK.

    i'm just running the code blane245 and it seems to be taking forever it wouldn't be looping would it ?

    wrightie.

  5. #5
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: macro to delete entire rows if certain cells are empty

    I received a type mismatch error when the macro eventually finished. The macro didn't remove any rows either.

  6. #6
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: macro to delete entire rows if certain cells are empty

    It's taking along time because it's looping through the entrie collection of 65000+ rows of the sheet. You could use the UsedRange property to restrict the loop:

    Please Login or Register  to view this content.
    Where is it getting a type mismatch error and are there rows with B, C, and D all empty? BTW, blanks are not empty.

    Could you send me an example and I will debug it.

  7. #7
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: macro to delete entire rows if certain cells are empty

    Hi Blane245,

    We receive monthly statements in a text file format and then import the file into excel. We end up with over 50,000 rows in excel so I've attached a small example for you.

    Originally I was removing all rows that had an empty cell in column B. But if you look at MRS bloggs statement I can't use that macro as it removes the Interest of 12 because they are on seperate rows.

    So I need a macro the same as Step4() but one that will only remove rows that have empty cells in B C &D.

    I hope this explains things better for you.

    Cheers
    Last edited by wrightie; 03-11-2009 at 04:32 PM.

  8. #8
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: macro to delete entire rows if certain cells are empty

    Oops, that example is password protected.

  9. #9
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: macro to delete entire rows if certain cells are empty

    Doh, sorry. Hope this one is ok for you.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: macro to delete entire rows if certain cells are empty

    Try Step6(). I deletes the rows from the bottom to the top.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: macro to delete entire rows if certain cells are empty

    All your help is much appreciated and this works great if it's only a couple of hundred rows.

    But with the amount of rows that I'm using it's just too slow.

    Is there anyway I can speed it up ?

    Cheers
    Wrightie

  12. #12
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: macro to delete entire rows if certain cells are empty

    There is another way to do this that I can think of, but it involves using another worksheet. Basically, you select all of the rows that you want to keep, copy them to another worksheet. Delete the original and then remain the new sheet to the old name. Does that cause you a problem? If not, I'll send you that code.

  13. #13
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: macro to delete entire rows if certain cells are empty

    No that wouldn't be a problem. I'm intrigued ....

  14. #14
    Forum Contributor
    Join Date
    09-18-2008
    Location
    india
    Posts
    158

    Re: macro to delete entire rows if certain cells are empty

    Hi,

    The other way is to give the range till the max rows rather than checking from last row.


    Regards,
    Shekar.

  15. #15
    Registered User
    Join Date
    08-21-2008
    Location
    Gibraltar
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: macro to delete entire rows if certain cells are empty

    I'm not sure how I would do that Shekar

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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