+ Reply to Thread
Results 1 to 4 of 4

find <> empty cell in range, delete entire row

  1. #1
    Registered User
    Join Date
    03-26-2011
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    2

    find <> empty cell in range, delete entire row

    This is my first post here, so welcome Everyone I'm just starting the adventure with programming and hope to learn more thanks to your experience (and count on your sense of humor and comprehension in case of lame questions ).

    My today's problem is as follows: I'm trying to write a macro that searches for not empty cell in given column and deletes entire row once the condition is fulfilled.
    So as to limit the range (not to search in entire column) I'm trying to specify last row in the table. However, the macro (see code below) is not working correctly - no error occurs but it still is not working as expected (deletes some rows, empty or not).

    If you could look into this and help me to understand where's en error in my way of thinking if would be great.


    Please Login or Register  to view this content.
    Many thanks in advance!
    Last edited by Shell_; 03-26-2011 at 12:38 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: find <> empty cell in range, delete entire row

    Try this

    Please Login or Register  to view this content.

    instead of this:

    Please Login or Register  to view this content.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: find <> empty cell in range, delete entire row

    Hi,

    You are effectively looping through your range of cells but your active cell never changes, hence whenever the loop If statement is true you are merely deleting the active cell row moving everything up.

    However looping through cells and then taking some action is essentially a time consuming process, particularly with large ranges since the VBE needs to keep jumping backwards and forwards to the Excel application.
    If there is some standard Excel functionality that will achieve the task then you should always use that. In this case it would be better to use Data Filter to filter the column for the values you want to identify and then delete all the filtered rows in one go.

    You can of course write a macro to automate the filter and delete.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    03-26-2011
    Location
    Europe
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: find <> empty cell in range, delete entire row

    Thanks a lot, it was a valuable remark for the beginner. I will use the filter.

+ Reply to Thread

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