Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
After selecting a range, say D5:D39
where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
As "#REF!" is no value but text, you could try to test for ISERROR(.Cells(r,1))
-- PeterS "JMay" schreef in bericht news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub --- Peter's outgoing mail is certified 100% Virus Free. (AVG) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11-9-2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
How "exactly" would I substitute ISERROR(.Cells(r,1)) into my existing code
below? a bit unsure here... TIA "PeterS" wrote in message ... As "#REF!" is no value but text, you could try to test for ISERROR(.Cells(r,1)) -- PeterS "JMay" schreef in bericht news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub --- Peter's outgoing mail is certified 100% Virus Free. (AVG) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 11-9-2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
If what you see in the cell is #REF!, then the cell contains an error value, not the text you
are seeing (and testing for). Try it this way: With .Cells(r, 1) If IsError(.Value) Then .EntireRow.Delete ElseIf .Value = 0 Then .EntireRow.Delete End If End With On Sun, 21 Sep 2003 07:46:04 -0400, "JMay" wrote: After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
Thanks Myrna, "I-Got-It!! (tks to you)"
"Myrna Larson" wrote in message ... If what you see in the cell is #REF!, then the cell contains an error value, not the text you are seeing (and testing for). Try it this way: With .Cells(r, 1) If IsError(.Value) Then .EntireRow.Delete ElseIf .Value = 0 Then .EntireRow.Delete End If End With On Sun, 21 Sep 2003 07:46:04 -0400, "JMay" wrote: After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
Sub DeleteRows()
Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete End With Next End With End Sub will check for #REF specifically Demo'd from the immediate window: ? activeCell.Text #REF! ? activeCell.Text = "#REF!" True -- Regards, Tom Ogilvy JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
Tks Tom for the clarification...
JMay "Tom Ogilvy" wrote in message ... Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete End With Next End With End Sub will check for #REF specifically Demo'd from the immediate window: ? activeCell.Text #REF! ? activeCell.Text = "#REF!" True -- Regards, Tom Ogilvy JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
Hi Tom
I was playing with union today and like to ask you this This is working fast and great for the whole column A Sub test2() Dim cell As Range, Rng As Range For Each cell In Range("A:A") If cell.Value = 0 Or cell.Text = "ron" Then If Rng Is Nothing Then Set Rng = cell Else Set Rng = Union(Rng, cell) End If End If Next If Not Rng Is Nothing Then Rng.EntireRow.Delete End If End Sub But if I change it to this If cell.Value = 0 Or cell.Text = "#REF!" Then I get an error 13 Can you tell me why Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete End With Next End With End Sub will check for #REF specifically Demo'd from the immediate window: ? activeCell.Text #REF! ? activeCell.Text = "#REF!" True -- Regards, Tom Ogilvy JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
Well thanks for pointing that out. The first check (cell.Value = 0 ) would
give you the type mismatch. it could be handled with If cell.Text = "0" Or cell.Text = "#REF!" Then so in JMay's case it would be the same alteration. -- Regards, Tom Ogilvy Ron de Bruin wrote in message ... Hi Tom I was playing with union today and like to ask you this This is working fast and great for the whole column A Sub test2() Dim cell As Range, Rng As Range For Each cell In Range("A:A") If cell.Value = 0 Or cell.Text = "ron" Then If Rng Is Nothing Then Set Rng = cell Else Set Rng = Union(Rng, cell) End If End If Next If Not Rng Is Nothing Then Rng.EntireRow.Delete End If End Sub But if I change it to this If cell.Value = 0 Or cell.Text = "#REF!" Then I get an error 13 Can you tell me why Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete End With Next End With End Sub will check for #REF specifically Demo'd from the immediate window: ? activeCell.Text #REF! ? activeCell.Text = "#REF!" True -- Regards, Tom Ogilvy JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
Using Union is fast with 65536 records by the way Tom
5-6 seconds -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Well thanks for pointing that out. The first check (cell.Value = 0 ) would give you the type mismatch. it could be handled with If cell.Text = "0" Or cell.Text = "#REF!" Then so in JMay's case it would be the same alteration. -- Regards, Tom Ogilvy Ron de Bruin wrote in message ... Hi Tom I was playing with union today and like to ask you this This is working fast and great for the whole column A Sub test2() Dim cell As Range, Rng As Range For Each cell In Range("A:A") If cell.Value = 0 Or cell.Text = "ron" Then If Rng Is Nothing Then Set Rng = cell Else Set Rng = Union(Rng, cell) End If End If Next If Not Rng Is Nothing Then Rng.EntireRow.Delete End If End Sub But if I change it to this If cell.Value = 0 Or cell.Text = "#REF!" Then I get an error 13 Can you tell me why Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete End With Next End With End Sub will check for #REF specifically Demo'd from the immediate window: ? activeCell.Text #REF! ? activeCell.Text = "#REF!" True -- Regards, Tom Ogilvy JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF! Halting my Macro
Hi Tom
You are right Tom I was testing with blocks of cells with the same value. But it was fun to play with.(I learn things today) I use the example you give with the dummy column also a lot. I think it is the best way to do it. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... It depends on how many unions you perform. I tested it where it unioned every other cell in a single column (one at a time) as I recall, on a 2.4Ghz machine and it took over 24 hrs to complete - and then crashed. I like to use a formula =if(condition,na(),"") fill it down, then do columns(1).specialcells(xlformulas,xlerrors).Entir eRow.delete Use a dummy column in your code to put in the formula, then delete the column after the above. -- Regards, Tom Ogilvy Ron de Bruin wrote in message ... Using Union is fast with 65536 records by the way Tom 5-6 seconds -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Well thanks for pointing that out. The first check (cell.Value = 0 ) would give you the type mismatch. it could be handled with If cell.Text = "0" Or cell.Text = "#REF!" Then so in JMay's case it would be the same alteration. -- Regards, Tom Ogilvy Ron de Bruin wrote in message ... Hi Tom I was playing with union today and like to ask you this This is working fast and great for the whole column A Sub test2() Dim cell As Range, Rng As Range For Each cell In Range("A:A") If cell.Value = 0 Or cell.Text = "ron" Then If Rng Is Nothing Then Set Rng = cell Else Set Rng = Union(Rng, cell) End If End If Next If Not Rng Is Nothing Then Rng.EntireRow.Delete End If End Sub But if I change it to this If cell.Value = 0 Or cell.Text = "#REF!" Then I get an error 13 Can you tell me why Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) If .Value = 0 Or .Text = "#REF!" Then .EntireRow.Delete End With Next End With End Sub will check for #REF specifically Demo'd from the immediate window: ? activeCell.Text #REF! ? activeCell.Text = "#REF!" True -- Regards, Tom Ogilvy JMay wrote in message news:Kcgbb.4441$AH4.1113@lakeread06... After selecting a range, say D5:D39 where d20 might have = d25 and d25 is blank the folowing code is stopping <<HERE-belowon d20 - haven't I provided for this? TIA, Sub DeleteRows() Dim r As Long With Selection For r = .Cells.Count To 1 Step -1 With .Cells(r, 1) HERE If .Value = 0 Or .Value = "#REF!" Then .EntireRow.Delete End With Next End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
halting macro | Excel Programming | |||
halting macro | Excel Programming |