Try this.....

Cells.Clear
ActiveWindow.FreezePanes = False

Cheers
Nigel


"Steve" wrote in message
...
Hi, The issue is that the macro does not remove color fills or font
colors, and it doesn't ungroup columns.

Also I get an error if a characteristic does not exist on the page. For
example, I get an error if I tell it to unfreeze panes when there are no
frozen panes.

Also, anyidea how I can apply this to all worksheets in a workbook?

Thanks so much!




Sub DeleteEmptySteve3()
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.Columns.Ungroup
ActiveSheet.Cells.Rows.Ungroup
ActiveSheet.Cells.Rows.Ungroup

With ActiveSheet.UsedRange
.Value = .Value
End With

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

With ActiveSheet.UsedRange
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
End With

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