+ Reply to Thread
Results 1 to 6 of 6

Search for empty cell and delete that row plus the next 19 rows even if they contain data

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Reading
    MS-Off Ver
    Excel 2003
    Posts
    11

    Search for empty cell and delete that row plus the next 19 rows even if they contain data

    Hi,
    Been using examples from this forum for a long time and usually able to amend them to suit my needs but this time I am really stumped.

    What I am trying to do seems simple enough and there are lots of examples on how to find blank and delete row.
    Is there way of saying delete x rows instead of .EntireRow.Delete??

    Here is what I am trying to achieve and maybe I am going about it the wrong way.
    Column A has row headers that repeat every 19 lines.
    They are names like this A1 CIName, A2 SerialNo A3 Supported and so on, then on the 20th row it starts again for however many records have been created
    I want to check all column B to see if the ID field is blank and if so delete all 19 rows down even if some columns have data in and then carry on to the next Blank column delete until end of rows.

    I am unable to post screen shots or files due to firewall restrictions.

    This is kind of how the data looks:
    A B C D E

    A1 CIName ABC123 CBD456 EFG567 and so on
    A2 SerialNo CDE567 balh blah and so on
    This continues down to line with other criteria 19 then line 20 starts again
    A20 CIName Blank
    A21 SerialNo Blank
    A23

    Solution:
    To check for blanks next to CIName and if found delete 19rows and continue down worksheet to end

    I cannot use the Autofilter method to find blanks as some of the rows in between may contain data and I want those to be deleted also.

    Would it be easier to Find CIName field check to see if Column B is blank and delete rows to next CIName field that is not blank?


    Simples!! but I cannot figure out how

    Any help greatly appreciated
    Hope it all makes sense.
    Last edited by aprcamb; 05-10-2013 at 07:54 AM.

  2. #2
    Registered User
    Join Date
    09-22-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    36

    Re: Search for empty cell and delete that row plus the next 19 rows even if they contain d

    In order to delete several rows at once you can use:
    Please Login or Register  to view this content.
    The above would delete rows 1 to 19.
    'Ignoramus et ignorabimus'

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Reading
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Search for empty cell and delete that row plus the next 19 rows even if they contain d

    Hi Red Spot,

    Thanks for the quickly reply and that does answer how to delete multiple lines instead of just a single row and would work but then I would have to keep repeating the same for the remaining rows.
    Ideally the code should check for used cells in column A find the CIName field, check to see if column B is blank and delete all rows down to the next CIName row where column B contains data, skip those rows and so on to end of spreadsheet in one continuous loop

    maybe an IF, Then statement may work better

    But you answered my initial question about deleting more than one row.
    So thanks very much for that.

  4. #4
    Registered User
    Join Date
    09-22-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    36

    Re: Search for empty cell and delete that row plus the next 19 rows even if they contain d

    Please Login or Register  to view this content.
    Goes from A1 to the last cell in column A, finds the first CIName. If nothing in Column B, 19 rows get deleted, otherwise it skips 19 rows and assumes there should be an other CIName.
    Untill it finds a CIName it checks every row though.

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    Reading
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Search for empty cell and delete that row plus the next 19 rows even if they contain d

    Mornign Red Spot,
    Just arrived into the office and tested the code that you sent over.
    Its works great and does exactly what I wanted.

    Thanks for taking the time to help.
    Cheers

  6. #6
    Registered User
    Join Date
    09-22-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    36

    Re: Search for empty cell and delete that row plus the next 19 rows even if they contain d

    You're welcome. Please do mark the topic as 'solved'.

+ 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