Object variable error
 

Hello,

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

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 Su

--
Ore
-----------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...nfo&userid=919
View this thread: http://www.excelforum.com/showthread.php?threadid=38081

Object variable error
 

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

Object variable error
 

K Dales ,

Thanks for the help! That worked perfectly. :)

Oreg


--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=380818