PDA

View Full Version : delete



oleg_v
04-12-2010, 05:27 AM
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
04-12-2010, 05:40 AM
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
04-12-2010, 05:43 AM
hi
sorry
i meant that cell value is not numbers and not letters

p45cal
04-12-2010, 05:51 AM
This one scrubs rows with empty cells in column B too, remove
IsEmpty(Cells(rw, "B")) Or
if you don't want this.

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

mdmackillop
04-12-2010, 05:58 AM
Try this

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

oleg_v
04-12-2010, 06:01 AM
hi
thanks

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

oleg_v
04-12-2010, 06:18 AM
i can not get it to work
it does not delete stuff like "---------------------"

p45cal
04-12-2010, 06:19 AM
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
04-12-2010, 06:34 AM
this one


Try this

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

p45cal
04-12-2010, 06:44 AM
I suspect a regex engine flavour problem with mdmackillop's solution.
Does mine work for you?

oleg_v
04-12-2010, 06:55 AM
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
04-12-2010, 07:48 AM
Works for me on your sample. What Excel version are you using?

GTO
04-12-2010, 08:23 AM
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
04-12-2010, 08:44 AM
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
04-12-2010, 11:54 AM
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

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

GTO
04-12-2010, 12:13 PM
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/library/1400241x(VS.85).aspx

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

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

mdmackillop
04-12-2010, 12:28 PM
Also
http://msdn.microsoft.com/en-us/library/ms974570.aspx

oleg_v
04-12-2010, 12:29 PM
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
04-12-2010, 12:32 PM
Google for RegExp . There is a lot out there.

GTO
04-12-2010, 12:36 PM
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

oleg_v
04-14-2010, 05:01 AM
hi
this works great i am sorry
i was not in the office to thank you properly

i have one more question
if i do not want to delete the rows if i want to copy them to another sheet

starting at "A1"


thanks

Try this

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

oleg_v
04-14-2010, 05:03 AM
i forgot
what i need to change in the macro in to answer the question in my prewios post

thanks

mdmackillop
04-14-2010, 05:15 AM
Record a macro copying and pasting to a new location. Substitute this for the Delete command. If you have problems, post your code.

oleg_v
04-14-2010, 05:29 AM
hi

it does not work
it says that the data is in different shape

the code:

Sub DelChars2()
Dim rng As Range, i As Long
Dim RegExp
Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
.Global = True
.IgnoreCase = True
End With
RegExp.Pattern = "(mm)"
Set rng = Intersect(Columns(2), ActiveSheet.Range("B2:B6"))
For i = rng.Cells.Count To 1 Step -1
If (RegExp.test(rng(i))) Then rng(i).EntireRow.Copy


Sheets("Sheet3").Select
Rows("72:72").Select
ActiveSheet.Paste
Next
End Sub


thnaks

mdmackillop
04-14-2010, 05:37 AM
Try stepping though the code to identify the problem.

oleg_v
04-15-2010, 04:10 AM
I am sorry

i am not succeeding

oleg_v
04-15-2010, 04:25 AM
hi

i have a question why the attached macro does not work

Sub DelChars2()
Dim rng As Range, i As Long, b As Integer
Dim RegExp
Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
.Global = True
.IgnoreCase = True
End With
RegExp.Pattern = "(mm)"
Set rng = Intersect(Columns(2), ActiveSheet.Range("B1:B10"))

For i = rng.Cells.Count To 1 Step -1
If (RegExp.test(rng(i))) Then rng(i).EntireRow.Copy


Sheets("Sheet3").Select
Sheets("sheet3").Range("a80").pastespetial

Sheets("Sheet1").Select

Next
End Sub

thanks

GTO
04-15-2010, 04:32 AM
Oleg,

Please stay with your first thread at http://www.vbaexpress.com/forum/showthread.php?t=31447&page=2

Imagine if you were answering questions only to find out others were answering the same question in another thread. Does that make sense?

Mark

oleg_v
04-15-2010, 04:40 AM
I did not know what to do my old thread was mark as "solved"

only because of that

sorry without knowing

mikke3141
04-15-2010, 04:43 AM
I got it work when i changed pastespetial to pastespecial :hi:

oleg_v
04-15-2010, 04:49 AM
it still
gives me an error when i want to run the macro several times and when i clean sheet3

mdmackillop
04-15-2010, 05:15 AM
There is a simple error here. Try and work out the logic of what is happening

For i = rng.Cells.Count To 1 Step -1
If (RegExp.test(rng(i))) Then rng(i).EntireRow.Copy
Sheets("Sheet3").Select
Sheets("sheet3").Range("a80").pastespecial
Sheets("Sheet1").Select
Next

oleg_v
04-15-2010, 05:23 AM
i am trying but without success

mdmackillop
04-15-2010, 05:35 AM
Does this not make more sense


x = 80
For i = rng.Cells.Count To 1 Step -1
If (RegExp.test(rng(i))) Then
rng(i).EntireRow.Copy
Sheets("Sheet3").Range("a" & x).pastespecial
x = x+1
end if
Next