+ Reply to Thread
Results 1 to 15 of 15

Delete Row if Contains Blank Cell

  1. #1
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Delete Row if Contains Blank Cell

    Hello

    Please see attachment for example data.

    I have a code that searches in column B and if the cell contains either the words "Employee" or "Overall Total:", then it deletes the entire row.

    Please Login or Register  to view this content.
    How can I edit the code so that it also deletes the row if the cell in B is blank?

    I have tried editing this line
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    but it deletes every row on the sheet.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Delete Row if Contains Blank Cell

    Try this...

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Delete Row if Contains Blank Cell

    No that didn't work. Thank you though.

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Delete Row if Contains Blank Cell

    For one, your loop stops if the employee name is blank.... I wonder if you are trying to delete the row if there is a blank employee name BEFORE the last row?

    If so, try this:

    Please Login or Register  to view this content.
    Last edited by Arkadi; 09-21-2018 at 08:16 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Delete Row if Contains Blank Cell

    You can use filter but you got be careful with the name of a table.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Delete Row if Contains Blank Cell

    Thank you for this, but it doesn't delete the blank cells.

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Delete Row if Contains Blank Cell

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Delete Row if Contains Blank Cell

    It's coming up with error message : Can't move cells in a filtered range or table.

    I've removed/cleared all filters and yet still won't work.

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Delete Row if Contains Blank Cell

    Have you tried my approach? (Post #4)

  10. #10
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Delete Row if Contains Blank Cell

    Yes - yours comes up with error 1004 : Delete method of Range class failed

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Delete Row if Contains Blank Cell

    I run the code and did not get an error. Could you attach the sample with the error?

  12. #12
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Delete Row if Contains Blank Cell

    This is the sample which gets the error message
    Attached Files Attached Files

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Delete Row if Contains Blank Cell

    I can't duplicate the error either... odd.

  14. #14
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Delete Row if Contains Blank Cell

    Figured it out guys! It's because I had hidden columns that must have had a filter on. Once I unhid and removed filter, it worked.

    Thank you both!

  15. #15
    Registered User
    Join Date
    09-06-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Delete Row if Contains Blank Cell

    Figured it out guys! It's because I had hidden columns that must have had a filter on. Once I unhid and removed filter, it worked.

    Thank you both!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Delete Row If Cell is Blank
    By ceres in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-01-2016, 09:52 AM
  2. [SOLVED] Macro Delete Contents in 23rd corresponding cell if the cell in range is a non-blank cell
    By murtaza.khan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-04-2014, 01:11 AM
  3. Replies: 1
    Last Post: 05-10-2012, 04:58 PM
  4. [SOLVED] To delete blank rows having blank cell in the needed raws
    By Alexander_Golinsky in forum Excel General
    Replies: 4
    Last Post: 05-04-2012, 03:11 PM
  5. Replies: 1
    Last Post: 02-17-2012, 10:11 AM
  6. [SOLVED] Delete a row if blank cell
    By mohd21uk via OfficeKB.com in forum Excel General
    Replies: 1
    Last Post: 05-04-2006, 08:45 AM
  7. Delete a row if blank cell
    By mohd21uk via OfficeKB.com in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-13-2006, 11:40 AM

Tags for this Thread

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