+ Reply to Thread
Results 1 to 17 of 17

Eliminate blank rows by shifting rows up

  1. #1
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184

    Eliminate blank rows by shifting rows up

    I have the code below that clears colums B-J and L-N in whatever row you activate a cell in. When a row gets cleared, how do I shift all the other rows up to eliminate blank rows in between the data real-time? I've deactivated the cut function already since this messes up references in the worksheet, so it would have to only use copy, paste, and clearcontents functions. Also, I don't want to DELETE any rows, just essentially shift the blank rows to the bottom. So when a row is cleared, all others shift up and the blank one goes to the end of the data that is available for data entry, so rows 17 to 116 are always available. But it must check to make sure that B-J and L-N are ALL blank, otherwise some wanted rows with one piece of information might get cleared. So at any given time, my range will always go from row 17 to 116. This is Excel 2007. Here is the code I have to clear rows that can be built upon. Thanks a million!

    Please Login or Register  to view this content.
    Also, is there a way to select multiple cells via ctrl button or dragging, and have this clear multiple rows?
    Last edited by jman0707; 11-06-2008 at 02:09 AM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966
    You could try

    Please Login or Register  to view this content.
    to get the multiple rows.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jman0707,

    This macro will check all the rows in the assigned range. If the columns "B:J" and columns "L:N" of a row are empty (no values and no formulae) the entire row is deleted and all the rows below are shifted up leaving no blank row.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658
    I'm not exactly sure from your explanation, but maybe,
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Thanks mrice, that works great!

    Leith, That is close to what I'm looking for. The only thing is that it actually deletes the row, whereas I just need them to shift up and leave the blanks at the bottom alone. I need rows 17 to 116 to ALWAYS be open and available for input. So instead of a delete, it has to be more of a copy and paste feel, and it needs to shift any blank rows to the bottom so that they don't get deleted. Essentially it will take any row that has data in it, shift it up so that the data will be solid in row 17 through whatever the last row is that has data. Then from the last row all the way through row 116 will be blank and ready for input. Hopefully that makes sense.

    When rows get deleted, the worksheet's cell references get all messed up.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jman0707,

    Thanks for the feedback. I was on the fence about which solution to post. Rolled the dice and ... snake eyes!

    Sincerely,
    Leith Ross

  7. #7
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Shg, it's not running for me. It debugs and highlights this line.

    Please Login or Register  to view this content.
    I'll paste the workbook in to see if that helps. Thanks!
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Haha! I know, I'm expecting a double 6 apparently! I have a hard time explaining it so hopefully this will help!

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello jman707,

    In my code change the IF statement below...
    Please Login or Register  to view this content.
    To this...
    Please Login or Register  to view this content.
    This will leave a blank row.

    Sincerely,
    Leith Ross

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658
    Oops.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Leath - I tried the code and it didn't clear the row that was selected, so it had nothing to move up. I inserted this line of code right after the worksheet unprotect to try and get that accomplished. Then it ran forever and I had to hit escape. When hitting the command button, it needs to first clear columns B-J and L-N of any row selected, and THEN shift the others up to fill in. I couldn't get it to clear first and then shift.

    Please Login or Register  to view this content.
    Last edited by jman0707; 11-04-2008 at 05:07 PM.

  12. #12
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Shg, i tried yours as well, and it just searches the active row to see if everything is blank. I need it to CLEAR all cells in the row (except for column K) and THEN shift all other rows up, without deleting them, so there are no blanks. Hope that makes sense!

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658
    I did that because you said,

    But it must check to make sure that B-J and L-N are ALL blank, otherwise some wanted rows with one piece of information might get cleared.

  14. #14
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    OK, I see. I wasn't clear. I want it to clear the active row's cells B-J and L-N. Then, the other cells up, it will eliminate all blank cells, so if there's another blank row that the user deleted manually and not using the button, it would get erased. But sometimes there might only be 1 piece of information in a certain row, and when the rows shift i didn't want rows like that to be eliminated. Only if they had NOTHING in them, which a row that was deleted by the button wouldn't. So it would delete the active row, and any others within the data that are totally blank. Sorry about that!

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658
    If you don't already have what you need, I'll try to help -- if you post a before-and-after that you did manually and illustrates exactly what you want.

  16. #16
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    We're very close! This code from leath looks like it will work. We're just missing the clearcontents of the active selection, and we're missing the shifting of the rows up at the end to elimate blank rows.

    Please Login or Register  to view this content.
    The only thing that I was unclear on is that I need any rows in the selection to be cleared FIRST, and then it moves all other rows up to that there are no blank rows in between rows that have information. I inserted this line of code right after the worksheet unprotect to try and get that accomplished.

    Please Login or Register  to view this content.
    Then it ran forever and I had to hit escape. I realized that it still doesn't accomplish the shifting either.

    When hitting the command button, it needs to FIRST clear columns B-J and L-N of any row selected (even multiple rows), and THEN shift the others up to fill in. I couldn't get it to clear first and then shift. To select the row, you just have to click on a cell in the row. That's why I was trying to use selection.entirerow.

    At any time, rows 17 through 116 need to be available for entry, so that's why we're clearing contents and not deleting any rows. And if there are any rows within the data that contain at least SOMETHING in B-J or L-N, it shouldn't get deleted, they have to be completely blank. Which Leath and your code accomplished, but I need the clearcontents first, and then the shift. Column K has a formula in it and needs to remain untouched for the clear and the shift. It will recalculate once the shift happens.

    Here's a print screen of before and after. I took out the middle one, and the bottom one will shift up like it shows in the second shot. Notice Client 3 doesn't have much info in the row, so it needs to make sure B-J AND L-N are empty in order to delete it. It won't in this instance because there's info in C,E, and L.

    And the only problem that might occur is that if it sees the blank cells at the end, it might keep shifting cells into infinity. It just needs to recognize if there are blank ones within the data.

    Hopefully it won't be too tough to tweak. I have another command button that needs to do the same thing, but with a formula as a trigger, so hopefully you'll be able to easily help me with that new thread too! Thanks so much!
    Attached Files Attached Files
    Last edited by jman0707; 11-05-2008 at 12:12 PM.

  17. #17
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    This might be a little too hard to explain. I'll think about it and post a new thread when I can make it a little easier!

+ 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