Object variable error

[Oreg]

Hello,

Using a macro to delete rows depending on blank cells in column A. At times,all cells in column A are blank and macro fails. Object variable not set error when this occurs. Is there an error statement I can insert to overcome this problem? Something like on error resume next ?

Thanks !

Sub DeleteRowsThatLookEmptyinColA()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Delete
End If
Next
done:
Application.Calculation = xlCalculationAutomatic
End Sub

[K Dales]

Have you tried this:
Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
If Not (Rng is Nothing) Then...
For ix = Rng.Count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Delete
End If
Next
End If


"Oreg" wrote:

>
> Hello,
>
> Using a macro to delete rows depending on blank cells in column A. At
> times,all cells in column A are blank and macro fails. Object variable
> not set error when this occurs. Is there an error statement I can
> insert to overcome this problem? Something like on error resume next
> ?
>
> Thanks !
>
> Sub DeleteRowsThatLookEmptyinColA()
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual 'pre XL97 xlManual
> Dim Rng As Range, ix As Long
> Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
> For ix = Rng.Count To 1 Step -1
> If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
> Rng.Item(ix).EntireRow.Delete
> End If
> Next
> done:
> Application.Calculation = xlCalculationAutomatic
> End Sub
>
>
> --
> Oreg
> ------------------------------------------------------------------------
> Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
> View this thread: http://www.excelforum.com/showthread...hreadid=380818
>
>

[Oreg]

K Dales ,

Thanks for the help! That worked perfectly.

Oreg