Solved: delete rows which are not completely filled

[MNJ]

Hi all,
Thanks once again for answering my previous threads. But here i am with another Question.

I was wondering if I could programmically delete rows which are not fully filled to column J by using worksheet change?


Thanks. And have a great weekend.

[Simon Lloyd]

i suppose the easiest way would be to look for blanks in column J for then delete the entire row if it is blank, like this,

[vba]
Sub Remove_Row_Column_J_Blanks()
Dim JLastRow As Long
Dim i As Long
Application.ScreenUpdating = False
JLastRow = Cells(Rows.Count, "J").End(xlUp).Row'''''find used range in column j including blanks
For i = JLastRow To 1 Step -1''''work backwards from last row
If Cells(i, "J").Value = "" Then'''''if the cell numbered i in column J is blank then
Rows(i).Delete'''''delete the entire row numbered i
End If
Next i
Application.ScreenUpdating = True
End Sub
[/vba]Regards,
Simon

[mdmackillop]

Use CountBlanks in a loop to check for any empty cells, or if just for column J
[VBA]Sub Macro1()
Columns("J").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
[/VBA]

[MNJ]

Hi,

thanks for both of your replies. I've tried both and it works out fine.
However, for mdmackillop's code, my screen kept on flashing and updating. I tried screenupdating with worksheet change but no avail. May i know how do i get rid of this?


Thanks

[bhoulaha]

Try

[VBA] Sub DelEmptyRows()
Dim i As Long, iLimit As Long
iLimit = ActiveSheet.UsedRange.Rows.Count

Application.Calculation = xlCalculationManual
For i = iLimit To 1 Step -1
If Application.CountA(Cells(i, 1).EntireRow) = 0 Then
Cells(i, 1).EntireRow.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
iLimit = ActiveSheet.UsedRange.Rows.Count 'attempt to fix lastcell

End Sub[/VBA]

[Simon Lloyd]

MNJ, at the very top of your code after you declare your variables you need this line [VBA]Application.ScreenUpdating = False[/VBA]and before your End Sub you need this [VBA]Application.ScreenUpdating = True[/VBA]Regards,
Simon

[lucas]

Also note that if your using worksheet change that it will fire every time you change anything on the sheet...you might want to run it as a regular macro when you call it only.....

[MNJ]

Many thanks for your replies. I've worked it out .


,
MNJ

[johnske]

Also note that if your using worksheet change that it will fire every time you change anything on the sheet...you might want to run it as a regular macro when you call it only.....

Or as a Worksheet_Activate or Deactivate event...