PDA

View Full Version : Solved: delete rows which are not completely filled



MNJ
01-25-2007, 07:41 PM
Hi all,
Thanks once again for answering my previous threads. But here i am with another Question. : pray2:

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.


:hi:

Simon Lloyd
01-26-2007, 02:21 AM
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,


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
Regards,
Simon

mdmackillop
01-26-2007, 03:08 AM
Use CountBlanks in a loop to check for any empty cells, or if just for column J
Sub Macro1()
Columns("J").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

MNJ
01-28-2007, 07:08 PM
Hi,

thanks for both of your replies. I've tried both and it works out fine. :thumb
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 :bow:

bhoulaha
01-28-2007, 10:17 PM
Try

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

Simon Lloyd
01-29-2007, 05:06 AM
MNJ, at the very top of your code after you declare your variables you need this line Application.ScreenUpdating = Falseand before your End Sub you need this Application.ScreenUpdating = TrueRegards,
Simon

lucas
01-29-2007, 08:05 AM
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
01-30-2007, 07:14 PM
Many thanks for your replies. I've worked it out . :thumb


:beerchug: ,
MNJ

johnske
01-30-2007, 09:42 PM
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...