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

[RobbieC]

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

 

[Fluff]

If Col L always has something in it, then there are no "blank rows". Can you please explain what you mean?

 

[RobbieC]

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)

 

[Fluff]

So you want to delete rows in L1:Q50 only where col L is blank. Is that right?

 

[RobbieC]

Yes mate, that's it

 

[Fluff]

Ok, how about

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

 

[RobbieC]

Great stuff! Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback