+ Reply to Thread
Results 1 to 11 of 11

Delete all blanks rows in specific range on all worksheets

  1. #1
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Question Delete all blanks rows in specific range on all worksheets

    I have a Workbook with multiple identical worksheets.
    I need a VBA macro that will delete all blank rows in a specific range on all worksheets inside a Workbook

    example:
    range of data on all sheets - A10:D17
    This range has some rows that are blank and some rows contain the data.
    How to delete empty or blank rows in all worksheets in a single action?
    please see attach, thanks advance
    Attached Files Attached Files
    Last edited by Dumy; 01-01-2015 at 02:56 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Delete all blanks rows in specific range on all worksheets

    Didi you want to delete all the cells with blank rows?
    Last edited by davesexcel; 12-30-2014 at 09:30 AM.

  3. #3
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Canada Eh
    MS-Off Ver
    Excel 2010
    Posts
    199

    Re: Delete all blanks rows in specific range on all worksheets

    Try the following and let me know if it meets your requirements:
    Please Login or Register  to view this content.
    Click the * to give Rep to a post you like.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Delete all blanks rows in specific range on all worksheets

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Delete all blanks rows in specific range on all worksheets

    Thanks guys for both response
    Quote Originally Posted by davesexcel View Post
    Didi you want to delete all the cells with blank rows?
    Good question, I am in the beginning did a mistake with my issue
    Macro should delete only cells in the blank rows in the range (regardless of whether cells contain a formula or not)
    In my attached file, the macro should delete only cells that have a red background
    Macro must not delete the other columns out of range

    also: I need a VBA macro in which I will be able to independently change the macro if I use it for other situations. (example: range and specific code element)

    BTW: Happy New Year 2015 and good jobs in yours life

    [edit]: add attach
    Attached Files Attached Files
    Last edited by Dumy; 01-01-2015 at 05:49 AM.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Delete all blanks rows in specific range on all worksheets

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Delete all blanks rows in specific range on all worksheets

    @davesexcel, thank you for the answer
    But, there is something wrong with this macro, VBA does not delete my all cells in an empty row. In this my example, remain two rows are not deleted

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Delete all blanks rows in specific range on all worksheets

    You may try this to see if you get the desired result.

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  9. #9
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Delete all blanks rows in specific range on all worksheets

    You may also Try this:-
    Please Login or Register  to view this content.
    Edit:- Yes Dumy, I ran the above code 3 times to get the desired result.
    I believe Sir Sktneers solution will do the job well.
    Last edited by Vikas_Gautam; 01-01-2015 at 02:42 PM.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  10. #10
    Forum Contributor
    Join Date
    09-10-2010
    Location
    europe
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: Delete all blanks rows in specific range on all worksheets

    Quote Originally Posted by sktneer View Post
    You may try this to see if you get the desired result.
    Thank you for answer, this VBA is solution for my problem

    @Vikas_Gautam
    Thank you for answer, but this vba code don't solve my problem. There is remain two rows are not deleted

  11. #11
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Delete all blanks rows in specific range on all worksheets

    Yes, Dumy..
    Thats why I edited my post for this.
    Sir Sktneer's solution is perfect here.

+ 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 to delete entire row if first cell of it is empty for specific range and worksheets
    By roshanvmech in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-01-2014, 12:36 PM
  2. [SOLVED] Insert and Delete blanks rows so that there are 2 blanks rows between text rows
    By erniedawg in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-29-2013, 02:27 AM
  3. [SOLVED] [B][/B] Delete Rows in a specific range VBA
    By Markarmi in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-01-2013, 05:53 AM
  4. Delete rows for specific range
    By georgeanaprop in forum Excel General
    Replies: 2
    Last Post: 12-04-2009, 04:22 AM
  5. Delete specific rows, creating range and criteria using inputbox
    By gmccray in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2008, 02:00 PM

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