Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Hi everyone. I have a col that contains many other value such as: A1 contain 0 A2 - 544 A3 -0 A4 - 100 A5 -0 A6 -53 A7 -0 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
vumian wrote: Hi everyone. I have a col that contains many other value such as: A1 contain 0 A2 - 544 A3 -0 A4 - 100 A5 -0 A6 -53 A7 -0 I think your post is incomplete. What are you trying to do with your data? AR |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
I wanna delete the rows that contain zero value . thanks for ur help -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
I wanna delete the rows that contain zero value . thanks for ur help -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
I wanna delete the rows that contain zero value . thanks for ur help -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
How about applying data|filter|autofilter to column A.
Then show only the 0's. delete those visible rows remove the data|filter|autofilter vumian wrote: I wanna delete the rows that contain zero value . thanks for ur help -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Assume column A
Sub AAA() Dim rng As Range Columns(1).Replace What:="0", _ Replacement:="=na()", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False On Error Resume Next Set rng = Columns(1).SpecialCells(xlFormulas, xlErrors) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End If End Sub -- Regards, Tom Ogilvy "vumian" wrote: I wanna delete the rows that contain zero value . thanks for ur help -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Sub DeleteRows_With_Zero()
findstring = "0" Set B = Range("A:A").Find(What:=findstring, LookAt:=xlPart) While Not (B Is Nothing) B.EntireRow.Delete Set B = Range("A:A").Find(What:=findstring, LookAt:=xlPart) Wend End Sub Gord Dibben MS Excel MVP On Wed, 19 Jul 2006 12:27:58 -0400, vumian wrote: I wanna delete the rows that contain zero value . thanks for ur help |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
to: Tom Ogilvy Your function error coz you replace zero inside cell Ex: 500 -- 5()na()na to: Gord Dibben your function's great. ths muc -- vumia ----------------------------------------------------------------------- vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649 View this thread: http://www.excelforum.com/showthread.php?threadid=56292 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Change
LookAt:=xlPart, to LookAt:=xlWhole, vumian wrote: to: Tom Ogilvy Your function error coz you replace zero inside cell Ex: 500 -- 5()na()na to: Gord Dibben your function's great. ths much -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Not so great........deletes rows that contain 500 or 702
Changes to avoid that. Sub DeleteRows_With_Zero() findstring = "0" Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole) While Not (B Is Nothing) B.EntireRow.Delete Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole) Wend End Sub Gord On Wed, 19 Jul 2006 13:26:38 -0400, vumian wrote: to: Tom Ogilvy Your function error coz you replace zero inside cell Ex: 500 -- 5()na()na to: Gord Dibben your function's great. ths much |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Change LookAt:=xlPart, to LookAt:=xlWhole It do not work, ths ------------------------------------ i still use Gord Dibben's fx but How come it del only row 10 to dow ??? 1-9 no effect Help me pls. i'm a newbie only : -- vumia ----------------------------------------------------------------------- vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649 View this thread: http://www.excelforum.com/showthread.php?threadid=56292 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Oke, s u c c e s s f u l , t h s e v e r y o n e I must paste special by value after do function. I fail to understan why ? anyway, it run well. thank you -- vumia ----------------------------------------------------------------------- vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649 View this thread: http://www.excelforum.com/showthread.php?threadid=56292 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Thanks for the correction - I had intended xlWhole, but guess the fingers did
their own thinking. -- Regards, Tom Ogilvy "Dave Peterson" wrote: Change LookAt:=xlPart, to LookAt:=xlWhole, vumian wrote: to: Tom Ogilvy Your function error coz you replace zero inside cell Ex: 500 -- 5()na()na to: Gord Dibben your function's great. ths much -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
I see why Stella is upset with you! <vbg.
STELLLLLLLLLLLLLLLLLLAAAAAAAAAAAA (my Marlon Brando impression) Tom Ogilvy wrote: Thanks for the correction - I had intended xlWhole, but guess the fingers did their own thinking. -- Regards, Tom Ogilvy "Dave Peterson" wrote: Change LookAt:=xlPart, to LookAt:=xlWhole, vumian wrote: to: Tom Ogilvy Your function error coz you replace zero inside cell Ex: 500 -- 5()na()na to: Gord Dibben your function's great. ths much -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
Just to clarify............
My posting was not intended to make corrections to Tom Ogilvy's code or as a derogatory statement towards Tom. If you note, the previous post by OP stated............... to: Gord Dibben your function's great. ths much I re-posted stating "not so great" with reference to the code I had posted earlier and gave OP a corrected version. Gord On Wed, 19 Jul 2006 11:04:54 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Not so great........deletes rows that contain 500 or 702 Changes to avoid that. Sub DeleteRows_With_Zero() findstring = "0" Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole) While Not (B Is Nothing) B.EntireRow.Delete Set B = Range("A:A").Find(What:=findstring, LookAt:=xlWhole) Wend End Sub Gord On Wed, 19 Jul 2006 13:26:38 -0400, vumian wrote: to: Tom Ogilvy Your function error coz you replace zero inside cell Ex: 500 -- 5()na()na to: Gord Dibben your function's great. ths much |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Delete row with criteria ?
hi everyone, and now, i need to keep value of "Abc" in col, it mean keep these rows, in the value rest, del all row. thank you for help -- vumian ------------------------------------------------------------------------ vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494 View this thread: http://www.excelforum.com/showthread...hreadid=562920 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete row with criteria ? | Excel Programming | |||
How to Delete row with criteria ? | Excel Programming | |||
Delete rows with different criteria | Excel Programming | |||
Further help on delete criteria found | Excel Programming | |||
delete rows with criteria | Excel Programming |