Results 1 to 8 of 8

Thread: deleting blank 'rows' in range - not delete entire row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default deleting blank 'rows' in range - not delete entire row

    Hi there, I have a range, L1:Q50 - this is a 6 column range.

    I have data on some of the rows (for each row there is always something in column L, but not nessesarily in the other 5)

    I'm looking to move each row (of 6 cells) up so that there are no blank 'rows' separating them...

    I can't use EntireRow.Delete because this range is surrounded by other data

    I'm sure that I have done this before, but for the life of me I can't remember...

    If you can point me in the right direction, I'd be very grateful

    Thanks

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: deleting blank 'rows' in range - not delete entire row

    If Col L always has something in it, then there are no "blank rows". Can you please explain what you mean?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: deleting blank 'rows' in range - not delete entire row

    Sorry Fluff, I meant that if there is an entry within the range, column L will have a value (even if the other 5 columns don't)

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: deleting blank 'rows' in range - not delete entire row

    So you want to delete rows in L1:Q50 only where col L is blank. Is that right?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: deleting blank 'rows' in range - not delete entire row

    Yes mate, that's it

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: deleting blank 'rows' in range - not delete entire row

    Ok, how about
    Code:
    Sub RobbieC()
       Dim Rng As Range
       For Each Rng In Range("L1:L50").SpecialCells(xlBlanks).Areas
          Rng.Resize(, 6).Delete xlShiftUp
       Next Rng
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Dec 2016
    Posts
    345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: deleting blank 'rows' in range - not delete entire row

    Great stuff! Thanks Fluff

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: deleting blank 'rows' in range - not delete entire row

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •