+ Reply to Thread
Results 1 to 20 of 20

Removing empty rows from a range without changing worksheet layout

  1. #1
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Removing empty rows from a range without changing worksheet layout

    Hi everyone,

    I have a range within a complex worksheet, which displays rows of data spanning 7 columns ('C' to 'I') and 17 rows ('6' to '22').

    This range is surrounded by other cells containing data which is irrelevant to the question, aside from the fact that its location and structure needs to be maintained.

    In any given row, cells 'C' to 'H' will either all contain data or all be blank.

    Cell 'I' would never contain data if 'C' to 'H' are blank, but cell 'I' may or may not contain data if cells 'C' to 'H' are populated.

    As an example, let's say I have data in rows 6, 7, 8, 10 and 12. I need to find a way to remove rows 9 and 11 so that the populated rows are consecutive without any blank rows in between. I also need to maintain the structure of rows 23 and above, AND I cannot delete anything in columns 'J' and above so the solution must not delete entire rows from the worksheet.

    An example is attached. This has been frustrating me for hours!! Any input would be massively appreciated.

    Regards

    Andy
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    So you want to shift up rows not empty from column C to I
    What about the check box do we need to shift up its status to ?
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    Hi PCI, no just need to shift up columns C to I, not rows A or B.

    To clarify - the user populates C to H using a macro, one row at a time, and sometimes will then enter a status code into column 'I' manually if this information is known.

    Sometimes, the user will delete one or more rows and therefore leaves the gaps which need to be removed whilst preserving the overall layout of the worksheet.

    Hope this helps.

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Removing empty rows from a range without changing worksheet layout

    Try this
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    Thanks +mikerickson, this works in terms of maintaining the worksheet layout but removes most of the populated rows which I need to keep...

  6. #6
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    See next code
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    +PCI, I ger a syntax error on the following line:

    Range("C6").Offset(1, 0).Resize(UBound(WkTb, 1), UBound(WkTb, 2)) = WkTb End Sub

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    Houps a small mistake
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by PCI; 10-14-2018 at 02:35 PM. Reason: File attached

  9. #9
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    Thank you +PCI, that works beautifully

    Thanks also to +mikerickson for your input.

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    You are welcome, please to help.
    You noticed the starting range "C6" and the full range Range("C6:I22") to be ajusted for your next change....!

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Removing empty rows from a range without changing worksheet layout

    And here's another to try

    Please Login or Register  to view this content.
    Nevermind - this won't work with formulas
    Last edited by xladept; 10-14-2018 at 03:48 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  12. #12
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    +PCI, I have just realised that my example was missing an important detail.

    Two of the columns contain formulas, which must not be deleted when the empty rows are removed.

    Can you help with this? Sorry I missed it out before!

    Andy
    Attached Files Attached Files

  13. #13
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Removing empty rows from a range without changing worksheet layout

    I forgot one line
    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    Perhaps next code, exist some nicer one

    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    +mikerickson - this worked perfectly. Many thanks for looking at this again for me.

    +PCI - this worked, aside from removing the formulas from F19:G22.


    A huge thanks again to everyone who looked at and helped with this thread, I couldn't have got there without you

  16. #16
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    Last one with also more flexibility for your next range change: See Const
    Please Login or Register  to view this content.
    Last edited by PCI; 10-14-2018 at 04:41 PM.

  17. #17
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    +PCI - I changed StCol to 'L' and EndCol to 'V' and it nearly works with just one problem.

    After running the code, the formulas have disappeared from each of the blank rows, with the exception of the last row (row 22).

  18. #18
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    See next code.
    Note all empty rows must/should have formulas ...!


    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    09-28-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    141

    Re: Removing empty rows from a range without changing worksheet layout

    Absolutely perfect. You're a genius

  20. #20
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Removing empty rows from a range without changing worksheet layout

    You're a genius
    Not yet, this is for Guru, I am only good ....(;-))
    Enjoy Excel

+ 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] Removing rows which are empty
    By lovinguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2015, 05:56 AM
  2. [SOLVED] Removing empty rows from a set of data - Need some help please!
    By mxoxossg in forum Excel General
    Replies: 4
    Last Post: 01-24-2013, 10:06 AM
  3. Removing Empty Rows On Multiple Sheets With Different Ranges
    By zebra4 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-02-2012, 05:40 PM
  4. Removing empty rows
    By sentinela in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2010, 11:10 AM
  5. Removing empty rows with a macro
    By Mikie in forum Excel General
    Replies: 8
    Last Post: 10-12-2009, 06:15 PM
  6. Removing empty rows?
    By frenchman96 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-24-2007, 11:42 AM
  7. Removing Empty Rows and selecting Specific Rows
    By Jetheat in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-11-2005, 07:10 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