delete

[oleg_v]

hi

i need a help with a macro
i need that if cells value in column "B" sheet1 is non numeric or letter delete the entire row

thanks

[mdmackillop]

Try recording a macro using F5/Special to see if that produces the correct result. Your question is not very clear. Letter is by definition, non-numeric.

[oleg_v]

hi
sorry
i meant that cell value is not numbers and not letters

[p45cal]

This one scrubs rows with empty cells in column B too, remove
[VBA]IsEmpty(Cells(rw, "B")) Or[/VBA]
if you don't want this.

[VBA]Sub blah()
Set xxx = Intersect(ActiveSheet.UsedRange, Columns("B"))
For rw = xxx.Row + xxx.Rows.Count - 1 To xxx.Row Step -1
If IsEmpty(Cells(rw, "B")) Or Not IsNumeric(Cells(rw, "B")) Then ActiveSheet.Rows(rw).Delete
Next rw
End Sub
[/VBA]

[mdmackillop]

Try this
[VBA]
Option Explicit
Sub DelChars()
Dim rng As Range, i As Long
Dim RegExp
Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
.Global = True
.IgnoreCase = True
End With
RegExp.Pattern = "\w"
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
For i = rng.Cells.Count To 1 Step -1
If Not (RegExp.test(rng(i))) Then rng(i).EntireRow.Delete
Next
End Sub
[/VBA]

[oleg_v]

hi
thanks

when i run this macro it deletes every thing even if the cells value is a number or a letter

[oleg_v]

i can not get it to work
it does not delete stuff like "---------------------"

[p45cal]

hi
thanks

when i run this macro it deletes every thing even if the cells value is a number or a letter

which macro?

[oleg_v]

this one

Try this
[vba]
Option Explicit
Sub DelChars()
Dim rng As Range, i As Long
Dim RegExp
Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
.Global = True
.IgnoreCase = True
End With
RegExp.Pattern = "\w"
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
For i = rng.Cells.Count To 1 Step -1
If Not (RegExp.test(rng(i))) Then rng(i).EntireRow.Delete
Next
End Sub
[/vba]

[p45cal]

I suspect a regex engine flavour problem with mdmackillop's solution.
Does mine work for you?

[oleg_v]

hi
it seems nothing is working
i attached the example file
look at the column "B" and if the cell does not contain number or letters delete
the row.
only for if this condition exists delete the row else not

thanks

[mdmackillop]

Works for me on your sample. What Excel version are you using?

[GTO]

Hi Olag,

I didn't test Pascal's, but Malcom's worked for me as described (xl2003 currently, but leaving...).

I am curious if we are understanding. In the below, should all the rows above 'DIM_1' be deleted, or just the row below 'Flatness'?

Mark

[p45cal]

Bearing in mind clarification in msg#3, have tested md's solution and it works fine in xl2007 too. Mine will delete the wrong stuff.

[oleg_v]

hi

thanks this is working pefectly

can please explain to me how you macro is working line by line

please it is very important to me


thanks

Try this
[vba]
Option Explicit
Sub DelChars()
Dim rng As Range, i As Long
Dim RegExp
Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
.Global = True
.IgnoreCase = True
End With
RegExp.Pattern = "\w"
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
For i = rng.Cells.Count To 1 Step -1
If Not (RegExp.test(rng(i))) Then rng(i).EntireRow.Delete
Next
End Sub
[/vba]

[GTO]

Here are some links that I have found helpful reference Regular Expressions. I personally owe a big thanks to Pedro (pgc01) at mrexcel for his long suffering patience in explaining the basics.

http://msdn.microsoft.com/en-us/libr...1x(VS.85).aspx

http://www.aivosto.com/vbtips/regex.html

http://www.regular-expressions.info/tutorial.html

[mdmackillop]

Also
http://msdn.microsoft.com/en-us/library/ms974570.aspx

[oleg_v]

i need to to some studying and fast
myibe you can give me a link to a good book for me print out

thanks for all you help

i never forget

friend


thanks

[mdmackillop]

Google for RegExp . There is a lot out there.

[GTO]

Also
http://msdn.microsoft.com/en-us/library/ms974570.aspx

My appreciation as well Malcom; thank you. Already added to favorites to reference :-)

A great day to you and yours,

Mark