Delete Entire Row If Column C is Blank
 

I am trying to delete the entire row (and move all rows below up) if the
value in Column C for each row is blank. I have used the following code but
it throws up an Next Without For error, not sure where to go from here

Thanks



Sub DeleteZerosinIngredients()

With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim r As Range
For Each r In Selection

Sheets("Ingredient Mix").Select
With ActiveSheet
If Cells(r.Row, 3) = 0 Then r.EntireRow.Delete
Next

End With
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

Delete Entire Row If Column C is Blank
 

John

You may try the following

Columns("C:C").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

to replace
If Cells(r.Row, 3) = 0 Then r.EntireRow.Delete

HTH

noirk

--
Message posted from http://www.ExcelForum.com

Delete Entire Row If Column C is Blank
 

Thanks Norika

Tried it but still get the "Next Without For error"


"norika " wrote in message
...
John

You may try the following

Columns("C:C").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

to replace
If Cells(r.Row, 3) = 0 Then r.EntireRow.Delete

HTH

noirka


---
Message posted from http://www.ExcelForum.com/

Delete Entire Row If Column C is Blank
 

John wrote:
I am trying to delete the entire row (and move all rows below up) if
the value in Column C for each row is blank. I have used the
following code but it throws up an Next Without For error, not sure
where to go from here

Thanks



Sub DeleteZerosinIngredients()

With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim r As Range
For Each r In Selection

Sheets("Ingredient Mix").Select
With ActiveSheet
If Cells(r.Row, 3) = 0 Then r.EntireRow.Delete
Next

End With
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

I do it like this:

Sub deleterows()

'first select the column you want to analyse

For Each c In Selection

If Cells(c.Row, c.Column) = "" Then
c.EntireRow.Delete
Else
End If
Next c

End Sub

Delete Entire Row If Column C is Blank
 

Thanks but the code below still comes up with "Next without For" compile
error

Sub DeleteZerosinSalesMix()

With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With


For Each c In Selection

Sheets("Sales Mix").Select
With ActiveSheet


If Cells(c.Row, c.Column) = "" Then
c.EntireRow.Delete Shift:=xlUp
Else
End If
Next c

End With
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

Delete Entire Row If Column C is Blank
 

It worked for me without all your additional code in it.


John wrote:
Thanks but the code below still comes up with "Next without For"
compile error

Sub DeleteZerosinSalesMix()

With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With


For Each c In Selection

Sheets("Sales Mix").Select
With ActiveSheet


If Cells(c.Row, c.Column) = "" Then
c.EntireRow.Delete Shift:=xlUp
Else
End If
Next c

End With
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub