How to delete these rows ?

[vumian]

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
.
.
A200 -andmore

A number of row that i don't know before

Here, how to use VBA or macro to delete these rows which it doesn't contain "abc", and rows behind must be up.

Thank you for your help.

[johncassell]

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

[vumian]

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

[johncassell]

Hi,

Yes this is the part that I cannot do.

It is possible to find the cell which doesn't equal 'abc' and then delete it (i.e. move the cell up but then it will not look in the very 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 and shift the row up one.

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

Does this make sense?

John

[vumian]

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 worked great.
now , i share with you, and thinking about my problem with keep "text" above ?

thank you

[johncassell]

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

[vumian]

hi man,

great man,
oke, haha, ) how to integrate both into one function and work great.

thank you

[vumian]

hi man,

do you know this, help me pls

i've got this function to check another file opened ?

Public Function gbIsWBOpen(rsName As String) As Boolean
On Error Resume Next
gbIsWBOpen = Len(Workbooks(rsName).Name)
On Error GoTo 0
End Function

sub check_open
if gbIsWBOpen("abc.xls").active = true then
'do function
else
msgbox "error"
end if
end sub

what's wrong with this man ?
thank you.

[vumian]

hi man,

do you know this, help me pls

i've got this function to check another file opened ?

Public Function gbIsWBOpen(rsName As String) As Boolean
On Error Resume Next
gbIsWBOpen = Len(Workbooks(rsName).Name)
On Error GoTo 0
End Function

sub check_open
if gbIsWBOpen("abc.xls").active = true then
'do function
else
msgbox "error"
end if
end sub

what's wrong with this man ?
thank you.

[Ron de Bruin]

For other examples see
http://www.rondebruin.nl/delete.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"vumian" <[email protected]> wrote in message
news:[email protected]...
>
> hi man,
>
> no work man,
>
> how to integrate both into one function and work great.
>
> 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
>

[Ron de Bruin]

For other examples see
http://www.rondebruin.nl/delete.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"vumian" <[email protected]> wrote in message
news:[email protected]...
>
> hi man,
>
> no work man,
>
> how to integrate both into one function and work great.
>
> 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
>

[johncassell]

Sub checkisopen()
On Error Resume Next
If Application.Workbooks("abc.xls") Is Nothing Then
MsgBox ("abc.xls is not open")
Else
MsgBox ("abc.xls is open")
End If

End Sub

thanks

John

[Ron de Bruin]

Look at the example in my reply in your other thread

You can test if a file is open with this function

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Use like this

If bIsBookOpen("test.xls") Then


--
Regards Ron de Bruin
http://www.rondebruin.nl



"vumian" <[email protected]> wrote in message
news:[email protected]...
>
> hi man,
>
> do you know this, help me pls
>
> i've got this function to check another file opened ?
>
> Public Function gbIsWBOpen(rsName As String) As Boolean
> On Error Resume Next
> gbIsWBOpen = Len(Workbooks(rsName).Name)
> On Error GoTo 0
> End Function
>
> sub check_open
> if gbIsWBOpen("abc.xls").active = true then
> 'do function
> else
> msgbox "error"
> end if
> end sub
>
> what's wrong with this man ?
> 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
>