Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Error 91 - Object variable with block variable not set | Excel Programming |