delete

[oleg_v]

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

i forgot
what i need to change in the macro in to answer the question in my prewios post

thanks

[mdmackillop]

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]

hi

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

the code:

[VBA]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
[/VBA]

thnaks

[mdmackillop]

Try stepping though the code to identify the problem.

[oleg_v]

I am sorry

i am not succeeding

[oleg_v]

hi

i have a question why the attached macro does not work

[VBA]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[/VBA]

thanks

[GTO]

Oleg,

Please stay with your first thread at http://www.vbaexpress.com/forum/show...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]

I did not know what to do my old thread was mark as "solved"

only because of that

sorry without knowing

[mikke3141]

I got it work when i changed pastespetial to pastespecial

[oleg_v]

it still
gives me an error when i want to run the macro several times and when i clean sheet3

[mdmackillop]

There is a simple error here. Try and work out the logic of what is happening
[VBA]
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
[/VBA]

[oleg_v]

i am trying but without success

[mdmackillop]

Does this not make more sense

[vba]
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
[/vba]