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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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