After reading:

http://www.microsoft.com/office/comm...=&ptlist=&exp=

I have ajusted the code below to add some ErrCheck statements below, and I get the following error now:

Compile Error
Lable not defined

and the statement "On Error GoTo ErrCheck4" is highlighted.

Thanks for your help.



Sub DeleteEmptySteve5()

Dim sht As Worksheet

Rem Collect all the worksheets together.
For Each sht In ActiveWorkbook.Worksheets
sht.Select False
Next

Rem The workbook in now in "Group" mode.
Rem The settings below will apply to all those selected sheets.

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

Dim test1 As Boolean
Dim test2 As Boolean
Dim test3 As Boolean
Dim test4 As Boolean
Dim test5 As Boolean

With ActiveSheet.UsedRange
.Value = .Value
End With

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

Check1:
On Error GoTo ErrCheck1
ActiveWindow.FreezePanes = False
test1 = True

Check2:
On Error GoTo ErrCheck2
Rows.Hidden = False
Columns.Hidden = False
test2 = True

Check3:
On Error GoTo ErrCheck3
ActiveSheet.Cells.Rows.Ungroup
ActiveSheet.Cells.Rows.Ungroup
test3 = True

Check4:
On Error GoTo ErrCheck4
ActiveSheet.Shapes("Drop Down 1").Select
Selection.Cut
test4 = True

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

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

ErrCheck1:
Resume Check2

ErrCheck2:
Resume Check3

ErrCheck3:
Resume Check4

ErrCheck4:
Resume Check5

You have an End Sub statement above your labels, so the labels are not
within the scope of your procedure. Move the End Sub to the actual bottom of
the subroutine.

--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
After reading:


http://www.microsoft.com/office/comm...=&ptlist=&exp=

I have ajusted the code below to add some ErrCheck statements below, and I
get the following error now:

Compile Error
Lable not defined

and the statement "On Error GoTo ErrCheck4" is highlighted.

Thanks for your help.



Sub DeleteEmptySteve5()

Dim sht As Worksheet

Rem Collect all the worksheets together.
For Each sht In ActiveWorkbook.Worksheets
sht.Select False
Next

Rem The workbook in now in "Group" mode.
Rem The settings below will apply to all those selected sheets.

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

Dim test1 As Boolean
Dim test2 As Boolean
Dim test3 As Boolean
Dim test4 As Boolean
Dim test5 As Boolean

With ActiveSheet.UsedRange
.Value = .Value
End With

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

Check1:
On Error GoTo ErrCheck1
ActiveWindow.FreezePanes = False
test1 = True

Check2:
On Error GoTo ErrCheck2
Rows.Hidden = False
Columns.Hidden = False
test2 = True

Check3:
On Error GoTo ErrCheck3
ActiveSheet.Cells.Rows.Ungroup
ActiveSheet.Cells.Rows.Ungroup
test3 = True

Check4:
On Error GoTo ErrCheck4
ActiveSheet.Shapes("Drop Down 1").Select
Selection.Cut
test4 = True

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

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

ErrCheck1:
Resume Check2

ErrCheck2:
Resume Check3

ErrCheck3:
Resume Check4

ErrCheck4:
Resume Check5

Thanks very much for your response Tom. I wouldn't have figured that out since I erroneously thought the "end sub" belonged above the error checks.

Since my last post I've been working more with my macro. I bought a book Excel 2003 VBA Programmer's Reference and I've spent hours with it but it hasn't helped me develop this macro. I guess I'll have to get one of those dummy books.

Below is my updated attempt with my macro. I changed the error coding in a manner that I thought made more sense.

Now when I run the macro, I get a Run-Time Error 438 at
"With ActiveWorkbook.sht.UsedRange".

If I delete that selection, the code works until it gets to
"Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel"

But then it never seems to go beyond that and delete any rows. When I click F8 to see whats going on it seems to loop back and forth between those two lines and never continue on to the next part of the code.

And for all my efforts, I can't get any part of this macro to affect anything other than the active worksheet in the workbook. All other worksheets remain unaffected.

Tom, Nigel, all, thanks very much for your help.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx
My Macro as of 8/1:

Sub DeleteEmptySteve100()


Dim sht As Worksheets
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

With ActiveWorkbook.sht.UsedRange
.Value = .Value
End With

On Error Resume Next
ActiveWorkbook.sht
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0

On Error Resume Next
ActiveWindow.FreezePanes = False

On Error Resume Next
Rows.Hidden = False
Columns.Hidden = False

On Error Resume Next
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Columns.Ungroup

On Error Resume Next
ActiveWorkbook.sht.Shapes("Drop Down 1").Select
Selection.Cut

On Error Resume Next
For Each sht In ActiveWorkbook.sht
sht.Select False
Next
For Each Cel In Range("E1:E1000")
Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel

Firstrow = ActiveWorkbook.sht.UsedRange.Cells(1).Row
Lastrow = ActiveWorkbook.sht.UsedRange.Rows.Count + Firstrow - 1
With ActiveWorkbook.sht
.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

Steve,

Change

With ActiveWorkbook.sht.UsedRange
to
With sht.UsedRange



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Steve" wrote in message
...
Thanks very much for your response Tom. I wouldn't have
figured that out since I erroneously thought the "end sub"
belonged above the error checks.

Since my last post I've been working more with my macro. I
bought a book Excel 2003 VBA Programmer's Reference and I've
spent hours with it but it hasn't helped me develop this macro.
I guess I'll have to get one of those dummy books.

Below is my updated attempt with my macro. I changed the error
coding in a manner that I thought made more sense.

Now when I run the macro, I get a Run-Time Error 438 at
"With ActiveWorkbook.sht.UsedRange".

If I delete that selection, the code works until it gets to
"Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel"

But then it never seems to go beyond that and delete any rows.
When I click F8 to see whats going on it seems to loop back and
forth between those two lines and never continue on to the next
part of the code.

And for all my efforts, I can't get any part of this macro to
affect anything other than the active worksheet in the workbook.
All other worksheets remain unaffected.

Tom, Nigel, all, thanks very much for your help.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx
My Macro as of 8/1:

Sub DeleteEmptySteve100()


Dim sht As Worksheets
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

With ActiveWorkbook.sht.UsedRange
.Value = .Value
End With

On Error Resume Next
ActiveWorkbook.sht
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0

On Error Resume Next
ActiveWindow.FreezePanes = False

On Error Resume Next
Rows.Hidden = False
Columns.Hidden = False

On Error Resume Next
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Rows.Ungroup
ActiveWorkbook.sht.Columns.Ungroup

On Error Resume Next
ActiveWorkbook.sht.Shapes("Drop Down 1").Select
Selection.Cut

On Error Resume Next
For Each sht In ActiveWorkbook.sht
sht.Select False
Next
For Each Cel In Range("E1:E1000")
Cel.Value = Application.WorksheetFunction.trim(Cel.Value)
Next Cel

Firstrow = ActiveWorkbook.sht.UsedRange.Cells(1).Row
Lastrow = ActiveWorkbook.sht.UsedRange.Rows.Count + Firstrow -
1
With ActiveWorkbook.sht
.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