EntireRowBlank? Just cant make it work....

[pedie]

Current code which does not work is as below

Please advice.
Thanks in advance.

[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub EntireRowBlank()
Dim lr As Long
Dim i As Range
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
       
For Each i In Range("A1:A" & lr)
 If IsBlank(i.EntireRow) Then
  MsgBox "Yes"
 End If
Next i
End Sub

 

[Jonmo1]

Try

If Application.CountBlank(i.EntireRow) = Columns.Count Then

 

[Jonmo1]

Or this might be faster...

If Len(i) = 0 AND Cells(i.Row, Columns.Count).End(xlToLeft).Column = 1 Then

 

[pedie]

Thank Jon,

it is working, however wont delete all the blank rows...so i tried different way with your code and still stuck....


Please check it again for me...

[/FONT]
[FONT=Courier New]Sub IreRowBlank()
Dim lr As Long
Dim i As Long
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
       [/FONT]
[FONT=Courier New]For i = lr To 1 Step -1
 If Application.CountBlank(Cells(i, "A").EntireRow) = Columns.Count Then
  Cells(i, "A").EntireRow.Delete
 End If
Next i
End Sub

 

[Jonmo1]

Your code works fore me...

When you say it won't delete all the blank rows...
Does that mean it deletes SOME of them, or NONE at all?

This may suggest your blanks are not really blank...

 

[pedie]

Hi Jon, sorry if i confused you...
I didnt say it didnt work...it is working perfectly and i'm using your line...
The thing is that....i am looping from top to bottom and thus it wont delete all the rows....because if row 1 is blank when deleted row 1 is 1 so it goes to row 2 without deleting row 1 which is originally row2

Thanks again...
Pedie

 

[Norie]

Pedie

The last code you posted loops from bottom to top, which is what you should do.

 

[pedie]

Norie, yes it is looping from bottom to top, however i dont know why it does not delete all the rows that are completely blank...

Thanks again guys!

 

[Jonmo1]

Norie, yes it is looping from bottom to top, however i dont know why it does not delete all the rows that are completely blank...

Thanks again guys!

OK, so I'll ask again...

When you say it won't delete all the blank rows...
Does that mean it deletes SOME of them, or NONE at all?

This may suggest your blanks are not really blank...

 

[pedie]

Jon, some of time...

Thanks again.