Hi everyone.

I have a col that contains many other text such as:
A1 - abc
A2 - abc
A3 -abc
A4 - asdkalk
A5 -asdlakjd
A6 -asdkaj
A7 -anything

Hello, I can help you if what you need is:

if cell doesn't equal 'abc' then delete what is in the cell.

It would then require you to sort afterwards so that you don't have
loads of empty rows.

the code is

Sub subname()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value < "abc" Then
cell.Value = ""
Else
End If
Next
End Sub

---
if you want to delete the contents of the entire row then replace
cell.value = "" with 'cell.EntireRow.ClearContents'

Hope this helps

Thanks

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565629

Hello, I can help you if what you need is:

if cell doesn't equal 'abc' then delete what is in the cell.

It would then require you to sort afterwards so that you don't have
loads of empty rows.

the code is

Sub subname()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value < "abc" Then
cell.Value = ""
Else
End If
Next
End Sub

---
if you want to delete the contents of the entire row then replace
cell.value = "" with 'cell.EntireRow.ClearContents'

also, this code takes a while to run because it looks at every cell in
column A so you could implement a code which will stop the macro if the
next cell it looks in is empty..

---
Sub subname()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value = "" Then
End
Else
If cell.Value < "abc" Then
cell.EntireRow.ClearContents
Else
End If
End If
Next
End Sub
---
Hope this helps

Thanks

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565629

great, very thanks but i aslo get 1 little question more :)

1) your code delete OK, but deleted rows is not up. It mean it keep
orgianal position.
2) and now, pls help me, i wanna del from rows 2 to end of rows in
database
because the first row is title, man, :))
thank you very much for help


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=565629

Hi,

Yes this is the part that I cannot do.

It is possible to find the cell which doesn't equal 'abc' and the
delete it (i.e. move the cell up but then it will not look in the ver
next cell.

What I mean is..

if you have the following information:

cell A1 = Title
A2 = abc
A3 = abc
A4 = ggggg
A5 = ffffff
A6 = abc

and then using your code, replace 'cell.Value = ""' with

cell.entirerow.Delete Shift:=xlUp

this will find that cell A4 doesn't equal 'abc' and delete the row an
shift the row up one.

The problem is that the loop will check cell A5 next but A5 will no
equal 'abc' because it was moved up the column so it won't have foun
ffffff.

Does this make sense?

Joh

--
johncassel
-----------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...fo&userid=2501
View this thread: http://www.excelforum.com/showthread.php?threadid=56562

i have a code to del all zero as following:

Sub Del_zero()
findstring = "0"
Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole)
While Not (B Is Nothing)
B.EntireRow.Delete
Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole)
Wend
End Sub

it's work great.
now , i share with you, and thinking about my problem with keep "text"
above ?

thank you


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=565629

i have a code to del all zero as following:

Sub Del_zero()
findstring = "0"
Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole)
While Not (B Is Nothing)
B.EntireRow.Delete
Set B = Range("L:L").Find(What:=findstring, LookAt:=xlWhole)
Wend
End Sub

it's work great.
now , i share with you, and thinking about my problem with keep "text"
above ?

thank you


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=565629

using your code with mine (your code was very helpful to me by the way,
thank you :-))

this will look in all cells from cell A2 (so it ignores the title cell)
and change the value of the cell to 'delete' if it does not equal
'abc'.

when this is finshed it will then run your code which deletes the row
and shifts it up if the value equals 'delete'

---Sub subname()
Dim cell As Range
For Each cell In Range("A2:A65536")
If cell.Value = "" Then
Run ("Del_zero")
End
Else
If cell.Value < "abc" Then
cell.Value = "delete"
Else
End If
End If
Next
End Sub

Sub Del_zero()
findstring = "delete"
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
---

Thanks

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565629

Hello, I can help you if what you need is:

if cell doesn't equal 'abc' then delete what is in the cell.

It would then require you to sort afterwards so that you don't have
loads of empty rows.

the code is

Sub subname()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value < "abc" Then
cell.Value = ""
Else
End If
Next
End Sub

---
if you want to delete the contents of the entire row then replace
cell.value = "" with 'cell.EntireRow.ClearContents'

Hope this helps

Thanks

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565629