run-time error '1004': Application-defined or object-deifined error

I am getting this error with the following Macro any help would be
appreciated.


Sub Process_Service_Fees()

Dim RangeDepth As String
Dim RangeDepth2 As String

RangeDepth = Worksheets("Sheet1").Range("F3").Value

For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth)
' Iterate through each element.

If MyObject.Value = "" Then '
If Cell is empty
Rows(MyObject.Row & ":" & MyObject.Row).Select
'Delete the row
Selection.Delete Shift:=xlUp
'Shift next line up one
End If
Next

RangeDepth2 = CInt((Worksheets("Sheet1").Range("F2").Value) / 1.5)

For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth2)
' Iterate through each element.

If MyObject.Value = "" Then '
If Cell is empty
Rows(MyObject.Row & ":" & MyObject.Row).Select
'Delete the row
Selection.Delete Shift:=xlUp
'Shift next line up one
End If
Next

Columns("F:F").Select 'Delete empty columns.
Selection.Delete Shift:=xlToLeft ' These three columns are
always empty
'Columns("J:J").Select ' and do not change, unless
file layout
' Selection.Delete Shift:=xlToLeft ' changes.
'Columns("L:L").Select
' Selection.Delete Shift:=xlToLeft

End Sub

Which line is causing the error?
--
HTH...

Jim Thomlinson


"[email protected]" wrote:

> I am getting this error with the following Macro any help would be
> appreciated.
>
>
> Sub Process_Service_Fees()
>
> Dim RangeDepth As String
> Dim RangeDepth2 As String
>
> RangeDepth = Worksheets("Sheet1").Range("F3").Value
>
> For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth)
> ' Iterate through each element.
>
> If MyObject.Value = "" Then '
> If Cell is empty
> Rows(MyObject.Row & ":" & MyObject.Row).Select
> 'Delete the row
> Selection.Delete Shift:=xlUp
> 'Shift next line up one
> End If
> Next
>
> RangeDepth2 = CInt((Worksheets("Sheet1").Range("F2").Value) / 1.5)
>
> For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth2)
> ' Iterate through each element.
>
> If MyObject.Value = "" Then '
> If Cell is empty
> Rows(MyObject.Row & ":" & MyObject.Row).Select
> 'Delete the row
> Selection.Delete Shift:=xlUp
> 'Shift next line up one
> End If
> Next
>
> Columns("F:F").Select 'Delete empty columns.
> Selection.Delete Shift:=xlToLeft ' These three columns are
> always empty
> 'Columns("J:J").Select ' and do not change, unless
> file layout
> ' Selection.Delete Shift:=xlToLeft ' changes.
> 'Columns("L:L").Select
> ' Selection.Delete Shift:=xlToLeft
>
> End Sub
>
>

You seem to introduce cell from nowhere. The For Next loop uses MyObject,
then you test cell which has not been assigned.

Use Option Explicit, you will never regret it

--
HTH

Bob Phillips

<[email protected]> wrote in message
news:[email protected]...
> I am getting this error with the following Macro any help would be
> appreciated.
>
>
> Sub Process_Service_Fees()
>
> Dim RangeDepth As String
> Dim RangeDepth2 As String
>
> RangeDepth = Worksheets("Sheet1").Range("F3").Value
>
> For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth)
> ' Iterate through each element.
>
> If MyObject.Value = "" Then '
> If Cell is empty
> Rows(MyObject.Row & ":" & MyObject.Row).Select
> 'Delete the row
> Selection.Delete Shift:=xlUp
> 'Shift next line up one
> End If
> Next
>
> RangeDepth2 = CInt((Worksheets("Sheet1").Range("F2").Value) / 1.5)
>
> For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth2)
> ' Iterate through each element.
>
> If MyObject.Value = "" Then '
> If Cell is empty
> Rows(MyObject.Row & ":" & MyObject.Row).Select
> 'Delete the row
> Selection.Delete Shift:=xlUp
> 'Shift next line up one
> End If
> Next
>
> Columns("F:F").Select 'Delete empty columns.
> Selection.Delete Shift:=xlToLeft ' These three columns are
> always empty
> 'Columns("J:J").Select ' and do not change, unless
> file layout
> ' Selection.Delete Shift:=xlToLeft ' changes.
> 'Columns("L:L").Select
> ' Selection.Delete Shift:=xlToLeft
>
> End Sub
>

Jim,

The debugger indicates this line as the problem:

For Each MyObject In Worksheets("Sheet1").Range("A1=AD:A" & RangeDepth)
' Iterate through each element.

A co-worker was using this Macro without issue and it looks like
something changed.



Bob,

Happily I didn't create the Macro, I've been asked to fix it though.

What is the value of RangeDepth when the program fails (It should be the same
as the value in F3 on sheet 1). If that value is not an integer between 1
and 32,700 then this will crash as it can not define the range to be
traversed.
--
HTH...

Jim Thomlinson


"[email protected]" wrote:

> Jim,
>
> The debugger indicates this line as the problem:
>
> For Each MyObject In Worksheets("Sheet1").Range("A1Â*:A" & RangeDepth)
> ' Iterate through each element.
>
> A co-worker was using this Macro without issue and it looks like
> something changed.
>
>
>
> Bob,
>
> Happily I didn't create the Macro, I've been asked to fix it though.
>
>

I forgot to add as Bob refered to add the line "Option Explicit" at the top
of the code module (prior to any procedures) and the select Debug -> Compile.
As a guess it will take you to a variable that was not declared. Declare the
Variable (Dim ... as ...) and then repeat Compile Debug until the program
successfully compiles.
--
HTH...

Jim Thomlinson


"[email protected]" wrote:

> Jim,
>
> The debugger indicates this line as the problem:
>
> For Each MyObject In Worksheets("Sheet1").Range("A1Â*:A" & RangeDepth)
> ' Iterate through each element.
>
> A co-worker was using this Macro without issue and it looks like
> something changed.
>
>
>
> Bob,
>
> Happily I didn't create the Macro, I've been asked to fix it though.
>
>