Please help me improve macro to convert spreadsheet to tabular for
 

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

Please help me improve macro to convert spreadsheet to tabular
 

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