+ Reply to Thread
Results 1 to 13 of 13

remove lines if a cell is blank

  1. #1
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    remove lines if a cell is blank

    I have a large file with hundreds of sheets, what I want to do is remove a line only if a cell is blank.
    the macro/vba needs to do the following tasks:

    If Cell E86 is blank, then remove entire row 86 and go to next sheet,
    If Cell E86 contains data, then go to next sheet.
    continue the process until the end of all sheets in the file.

    Hoping this isn't too difficult.
    thanks in advance.
    Last edited by Christopherdj; 08-01-2013 at 10:09 PM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: remove lines if a cell is blank

    Try this:

    Please Login or Register  to view this content.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: remove lines if a cell is blank

    Thanks, sorry but can it be activated from the current sheet and continue to next sheet, rather than the entire workbook?
    I have some statistical data in a number of sheets at the front that may/may not have data at E86.

    thanks

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: remove lines if a cell is blank

    This is something I like to do in case there's a chance that the worksheets may get shuffled.
    Insert a new worksheet
    Name it Lists
    A1: SkipSheets
    ...under that cell, list the worksheet names you DON'T want to process
    ...Select that range...Home.format_as_table...select a style
    ...Table_tools.Design...TableName: tblSkipList

    Now, this macro will process all worksheets that are NOT on that list

    Please Login or Register  to view this content.
    Does that approach work for you?

  5. #5
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: remove lines if a cell is blank

    Hey I thought it was working, but I get an error message.
    "error: Application-defined or object-defined error"
    Obviously there is something wrong. Any chance of fixing it for me?
    thanks

  6. #6
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: remove lines if a cell is blank

    Possibly...Does it happen all the time? Only when there is no data? When there are blank cells in the referenced column?
    Can you post a sample workbook that exhibits the problem?

  7. #7
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: remove lines if a cell is blank

    There is too much data to remove for me to provide a sample.
    Can you provide me with a simpler version that is more like a macro?
    go to E86, if blank delete row 86, go to next sheet

    then repeat the process?

  8. #8
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: remove lines if a cell is blank

    Ok...Try this
    Please Login or Register  to view this content.
    Last edited by Ron Coderre; 07-31-2013 at 09:29 AM.

  9. #9
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: remove lines if a cell is blank

    Your solution does not work, should it be clear contents or delete row in the script???


    I've uploaded a sheet, macro enabled. My real data has over 300 sheets.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    07-12-2013
    Location
    Los angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    109

    Re: remove lines if a cell is blank

    Hey, christopher try using:
    Please Login or Register  to view this content.
    This should delete the row like I think you are intending to.

  11. #11
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: remove lines if a cell is blank

    I've replaced the line with your text aloha31, but it does not work. Running the debug it identifies the End If as an issue. When I read the script it says go to E86, if empty delete entire row, then end if ....... but what does it do if the cell has data? It needs to check the cell at E86 and continue deleting (mostly only once), until E86 contains data, then go to the next sheet and repeat the process. Some sheets will have data in the cell, some won't. Can you fix the script using the file I uploaded?

  12. #12
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: remove lines if a cell is blank

    Aha! Finally, I comprehend what you're asking for!
    Try this code:
    Please Login or Register  to view this content.
    Does that help?

  13. #13
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Excel 2020
    Posts
    308

    Re: remove lines if a cell is blank

    We have victory !!! Thanks heaps. This will save me hours - seems to be working fine now. Excellent work, it is easy when we understand the questions and tasks to perform, sometimes it is difficult to communicte via written word. But this is good stuff. thanks again.

+ 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. How do I remove blank lines in the first column.
    By SWMagic in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-29-2013, 05:14 AM
  2. Create List and remove blank Lines
    By jamjam200 in forum Excel General
    Replies: 0
    Last Post: 01-22-2013, 12:30 PM
  3. Remove Blank Lines
    By Robotacha2010 in forum Excel General
    Replies: 5
    Last Post: 08-16-2010, 11:59 AM
  4. Remove Blank Lines
    By Nigel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2005, 11:00 AM
  5. [SOLVED] remove blank lines from an entire spreadsheet
    By mdeanda in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-21-2005, 09:06 AM

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