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?
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
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.
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.