Blank Rows deletion - Overlapping Sections error

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

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!

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

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.

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

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

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

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......