Oh, not intended, my sincere apologies.

Should have been:

Cells.ClearFormats

Too many late nights!!

Cheers
Nigel

"Steve" wrote in message
...
Hi Nigel, your command took care of the problem, but it also deleted every
single cell in the sheet! haha

I figured it out... this works for now...

Thanks also to Ron de Bruin webpage http://www.rondebruin.nl


Sub DeleteEmptySteve4()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

ActiveWindow.FreezePanes = False

Rows.Hidden = False
Columns.Hidden = False

ActiveSheet.Cells.Rows.Ungroup
ActiveSheet.Cells.Rows.Ungroup

With ActiveSheet.UsedRange
.Value = .Value
End With

ActiveSheet.Shapes("Drop Down 1").Select
Selection.Cut

ActiveSheet.Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
ActiveCell.Select

For Each cel In Range("E1:E1000")
cel.Value = Application.WorksheetFunction.trim(cel.Value)
Next cel

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "C").Value = "Volume" Or _
.Cells(Lrow, "C").Value = "Gross-margin-target-$-per-gallon" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-per-gallon" Or
_
.Cells(Lrow, "C").Value = "Gross-margin-target-$-total" Or _
.Cells(Lrow, "C").Value = "Economic-profit-target-$-total" Or _
.Cells(Lrow, "g").Value = "" Then .Rows(Lrow).Delete
'Or use this if you want to check more values.

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


"Nigel" wrote:

Try this.....

Cells.Clear
ActiveWindow.FreezePanes = False