How to Delete row with criteria ?

  • Thread starter Thread starter vumian
  • Start date Start date
V

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
 
vumian said:
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
Click to expand...

I think your post is incomplete. What are you trying to do with your
data?

AR
 
How about applying data|filter|autofilter to column A.

Then show only the 0's.
delete those visible rows
remove the data|filter|autofilter
 
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
 
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
 
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
 
Change
LookAt:=xlPart,
to
LookAt:=xlWhole,


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
Click to expand...
 
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
 
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 :
 
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
 
Thanks for the correction - I had intended xlWhole, but guess the fingers did
their own thinking.

--
Regards,
Tom Ogilvy


Dave Peterson said:
Change
LookAt:=xlPart,
to
LookAt:=xlWhole,
Click to expand...
 
I see why Stella is upset with you! <vbg>.

STELLLLLLLLLLLLLLLLLLAAAAAAAAAAAA
(my Marlon Brando impression)

Tom said:
Thanks for the correction - I had intended xlWhole, but guess the fingers did
their own thinking.
Click to expand...
 
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...............

I re-posted stating "not so great" with reference to the code I had posted
earlier and gave OP a corrected version.


Gord
 
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
 
Back
Top