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

 

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

Hope this helps

Thanks

John

 

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

Hope this helps

Thanks

John

 

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

 

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

thank you

 

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

 

[vumian]

hi man,

no work man,

how to integrate both into one function and work great.

thank yo

 

[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

 

[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

 

[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

Joh