How to Delete row with criteria ?

[vumian]

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
.
.
A133 -645
.
.
A199 -0
A200 -0

A number of row that i don't know before, but database maybe limit at row of 200

Here, how to use VBA or macro to delete these rows and rows behind must be up.

Thank you for your help.

[ducky]

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

[vumian]

I wanna delete the rows that contain zero value .
thanks for ur help

[Dave Peterson]

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

[Tom Ogilvy]

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
>
>

[Dave Peterson]

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

[Tom Ogilvy]

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
>
>

[Gord Dibben]

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
<[email protected]> wrote:

>
>I wanna delete the rows that contain zero value .
>thanks for ur help

[vumian]

to: Tom Ogilvy

Your function error coz you replace zero inside cell
Ex: 500 --> 5()na()na

to: Gord Dibben

your function's great with one col only
i apply your fx in my workbook with 2 value col, minus them return 0 or >0,it works well
i do it in my workbook with many cols, why it dont del form fisrt row ? actually, it del from row number 6 to down ?
ths much

[Dave Peterson]

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

[Gord Dibben]

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
<[email protected]> 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]

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 down ???

1->9 no effect

Help me pls. i'm a newbie only :P

[vumian]

Oke, successful, ths everyone

I must paste special by value after do function. I fail to understand why ?
anyway, it run well. thank you.

[Tom Ogilvy]

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]

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

[Gord Dibben]

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
><[email protected]> 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

[Gord Dibben]

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
><[email protected]> 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]

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