delete

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

Quote:

---

Originally Posted by 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]

---

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

thanks

Record a macro copying and pasting to a new location. Substitute this for the Delete command. If you have problems, post your code.

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

Try stepping though the code to identify the problem.

I am sorry

i am not succeeding

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

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

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

only because of that

sorry without knowing

I got it work when i changed pastespetial to pastespecial :hi:

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

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]

i am trying but without success

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]