Eliminate blank rows by shifting rows up

[jman0707]

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?

[mrice]

You could try

Please Login or Register  to view this content.

to get the multiple rows.

[Leith Ross]

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

[shg]

I'm not exactly sure from your explanation, but maybe,

Please Login or Register  to view this content.

[jman0707]

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.

[Leith Ross]

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

[jman0707]

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!

[jman0707]

Haha! I know, I'm expecting a double 6 apparently! I have a hard time explaining it so hopefully this will help!

[Leith Ross]

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

[shg]

Oops.

Please Login or Register  to view this content.

[jman0707]

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.

[jman0707]

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!

[shg]

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.

[jman0707]

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!

[shg]

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.

[jman0707]

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!

[jman0707]

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!