Specify Range of Rows xlshiftup

[Ace71425]

See code below...

code
Sub RemoveBlankCells()
Dim rng As Range
Set rng = Range("A1:D5000").SpecialCells(xlCellTypeBlanks)
rng.Rows.Delete Shift:=xlShiftUp
End Sub
/code

Code works good for what i want it to do but I dont want all the rows to shift up i.e. a, b, c, d is blank I want it to shift up those rows but not e, f, etc.

Thanks!

 

[Joe4]

So, if you have a row in which only column B is blank, do you want to delete/shift up just column B, or columns A through D?

 

[Ace71425]

Essentially the way the sheet is set up there would never be a circumstance where a, b, c, d was blank on it's own...so in other words...if a,b,c,d is blank - shift rows up is what im trying to accomplish.

 

[Joe4]

If it is simply a matter that either all cells in a,b,c,d will be blank, or none of them will, it is a very simple fix.
Just remove the .Rows from your delete line of code, as you just want to delete the highlighted range, not the whole row, i.e.

[COLOR=#333333]rng.Delete Shift:=xlShiftUp[/COLOR]

 

[Ace71425]

That sounded like it should have worked but didnt...basically heres the problem I have buttons on column E that control the sheet. If there happens to be a blank row of a,b,c,d that are on the same line as one of the buttons which go down about 25 rows or so then the button gets deleted because that row is getting deleted where the button is at. I'm not sure why your suggestion didnt work because it made sense it should only be moving up rows a-d?

 

[Joe4]

That doesn't make sense. It seemed to work for me.
See if you can prove it works by doing the following:
Try putting values like row numbers in some blank column off to the right. Start off by typing 1 in row 1, 2 in row 2, and then highlight those two rows and autofill down for all rows of data.
Now, run the macro. If it correctly deletes columns A-D for those blank rows, this new column should be totally undisturbed, and there should be no gaps in your row number.

If that works, then the issue may reside with how those buttons are set up.

 

[Ace71425]

It's weird because it doesnt mess with anything in the J column when I did the test you asked but it totally jacks up the buttons in the e column. I'm kinda at a loss right now. Maybe ill just try scooting those buttons over to a different column

 

[Joe4]

Make sure that the edge of them doesn't spill over into column D.
Do they span more than one cell? If so, that may be part of the problem.