MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Blank Rows deletion - Overlapping Sections error


Posted by Nat on April 18, 2001 3:42 AM

I am trying to delete a Blank Row using
myrange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
When Applying this to a range say myrange=A1 to G 2000
I get the error "The Command cannot be used on Overlapping Selections"

Can somebody Please Help

Tx in advance

Nat


Posted by Dave Hawley on April 18, 2001 4:03 AM


Hi Nat

Try this way.


Sub DeleteAllBlanks()
Dim MyRange As Range


Set MyRange = Range("A1:G100")
MyRange.SpecialCells(xlCellTypeBlanks).Delete

End Sub


Dave

OzGrid Business Applications

Posted by RJW on April 18, 2001 4:58 AM

Dave


Nat
In an earlier post, Mr. D. Hawley asked how many times have I supplied free help on this board. Well, here's my first attempt at it.

Are you trying to delete rows in range A1:G1000 if all cells in a row for columns A through G are blank?
If so, I think the following macro should do it (although there may be more efficient code for doing it).

Sub DeleteBlankRows()
Columns("A:A").Insert
With Range("A1:A2000")
.FormulaR1C1 = "=IF(COUNTA(RC[1]:RC[7])=0,1,"""")"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
End With
Columns("A:A").Delete
End Sub

The only reason for suggesting this macro is that I tried D. Hawley's macro but it didn't appear to produce the desired result - but, of course, I may not have understood what you are trying to do.

If you are trying to delete rows if any cell in a row for columns A through G is blank then try the following (again, there might be a better way of writing it).

Sub DeleteBlankRows()
Columns("A:A").Insert
With Range("A1:A2000")
.FormulaR1C1 = "=IF(COUNTA(RC[1]:RC[7])<>7,1,"""")"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
End With
Columns("A:A").Delete
End Sub

Now I too can boast about all the free help I give!


Posted by Dave Hawley on April 18, 2001 5:10 AM

I suggest you get a life RJW and stop worrying about mine!
OzGrid Business Applications

Posted by RJW on April 18, 2001 5:20 AM


That's a bit rich, old chap! I've just come across one of your messages on this board where you advised someone that if they can't take it then not to dish it out. I suppose you wish to retract that advice.

Posted by Dave Hawley on April 18, 2001 5:56 AM

On the contrary!

That's a bit rich, old chap! I've just come across one of your messages on this board where you advised someone that if they can't take it then not to dish it out. I suppose you wish to retract that advice.

Hey snap! I found your initial dishing out "a bit rich"

So I think my previous "advise" is very apt in your case too!

DaveOzGrid Business Applications

Posted by Nat on April 18, 2001 6:05 AM

Thanks Guys - But Dave your idea didnt work the way we wanted

Dave & RJW ,

Glad to provide some pastitme there !

coming to my issue , the deletion of the cell mixes up the rows by pushing up by one row..
So i ended up with completely zonked output.
Any flags to say i want the entire row removed-
and not have overlapping selections - Btw what is overlapping selections


Regards
Nat

Posted by Nat on April 18, 2001 6:19 AM

RjW,

I think you got the issue correct with the check for the error message and Congrats on your first attempt at helping, i thing it should work. Can you throw light on the Overlapping Selections part of the error message.

Regards

Nathan

Posted by RJW on April 18, 2001 6:26 AM

Good news! Our short acquaintance is at an end!

Hey snap! I found your initial dishing out "a bit rich" So I think my previous "advise" is very apt in your case too! Dave


Bit of a "Catch 22" that, DH.
Anyway,this is all getting a bit silly.
I hereby quit this board for other pastures and to follow my new vocation of dishing out free advice. (Hoping to exceed DH's average of 20 per day pretty quickly!).
Will probably also do something about DH's advice to "get a life", if I have the time. If not, will just have to get by without one - Oh well, never mind. Bye......